|
Todd,
Okay your basic idea is fine but why use a data step tha uses call execute
to create the SQL.
Use SQL to create a macro that houses the dataset list and use a %let
statement and sqlobs to get the number of datasets in that list. Then use
macro code to process this (macro array) over the appropriate code to
generate the unique datasets.
Something like the following:
<<<<untested>>>>
%macro distinct(lib = , mem = , var= ) ;
%local i stop ds_list ;
proc sql noprint :
select memname into : ds_list separated by " "
from dictionary.tables
where libname = %upcase(&lib) and memname =: %upcase(%unquote(&mem)) ;
%let stop = &sqlobs ;
quit ;
%do i = 1 %to &stop ;
proc sort
data = %scan(&ds_list,&i) nodupkey
out = new_%scan(&ds_list,&i) ;
by pid ;
run ;
%end ;
%mend distinct ;
%distinct(lib= X , mem= %quote(< M) , var=pid)
%distinct(lib= X , mem= %quote(<= M) , var=pid)
Toby Dunn
From: Todd Case <Todd.Case@FRX.COM>
Reply-To: Todd.Case@FRX.COM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Remove unwanted spaces from macro variable
Date: Mon, 14 Feb 2005 09:42:09 -0500
Hi Toby,
Thanks, I was trying to make this the most direct question I could, the
macro works:-). Maybe it would help if I describe the end results then you
could understand why I'm doing it this way (but thanks for your effort).
I'm trying to get the number of distinct obs from each dataset in the
library (and make the corresponding dataset for each one) without actually
referencing the datasets, its not necessary to do this since I need them
all. Please see my comments.
Thanks!
Todd
-----Original Message-----
From: toby dunn [mailto:tobydunn@hotmail.com]
Sent: Monday, February 14, 2005 9:32 AM
To: Case,Todd; SAS-L@LISTSERV.UGA.EDU
Subject: Re: Remove unwanted spaces from macro variable
Todd,
O were to start, well let me try here embedded comments:
macro splitit(ds=, break=);
proc sql;
select distinct(memname) into &ds separated by " ' ' " from
<you should put your colon after "into" as hard coded rather than soft
coded>
OK but this works-I'm trying to solve the immediate problem here
dictionary.columns
where compress(upcase(libname))='X'
<consider passing the data set name as a parameter rather than hard coding
it>
not a dataset, this is a libname and I want all the members
and substr(memname,1,1) &break;
<missing quit statement>
sorry I must not have copied the entire macro- I stress the macro works I'm
trying to get rid of the spaces, not make the macro work
%mend splitit;
%splitit(ds=:dsname1, break= %str(< 'M'))
%splitit(ds=:dsname2, break= %str(>= 'M'))
<if it was me I would roll the rest up into your macro rather than trying to
do it through a data step>
data dummy;
<use a data _nuill_ instead of a named data step>
array d2 {&dsnum} $ (&ds1. &ds2.); PROBLEM!
<you have no macro variables defined with the code you provided as ds1 and
ds2, you have dsname1 and dsname2>
sorry there is an intermediate step I didn't add which encloses the entire
string in single quotes
do i=1 to &dsnum.;
<have no clue where you are getting &DSNUM from its a mystery to me>
call execute
("proc sql; title3 " || trim(d2(i)) || "; select count(distinct(pid))
from X." || trim(d2(i)) || "; quit;
proc sort data=X." || left(d2(i)) || " nodupkey out=" || left(d2(i))
|| "; by pid; run;") ;
end;
<still not sure why you are doing this through a data step rather than
rolling it up into your SQL statement and macro>
run;
In short I see this as a lack of good macro design, lack of some of the best
practices in SAS, and lack of macro knowledge. Our master Ian, Ron, or
Richard should be answering this question and probrably could do a better
job at it than I.
Toby Dunn
From: Todd Case <Todd.Case@FRX.COM>
Reply-To: Todd.Case@FRX.COM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Remove unwanted spaces from macro variable
Date: Mon, 14 Feb 2005 09:10:05 -0500
Sorry want to mention I'm using V9.1.3 - this note about the 'meaning of an
identifier...' does not arise in earlier versions.
-----Original Message-----
From: Case,Todd
Sent: Monday, February 14, 2005 9:09 AM
To: 'SAS(r) Discussion'
Subject: Remove unwanted spaces from macro variable
In order to avoid the following note:
NOTE 49-169: The meaning of an identifier after a quoted string may change
in a future SAS release. Inserting white space between a quoted string and
the succeeding identifier is recommended.
I created some code which puts single quotes around each dataset name with a
space before after each dataset and before the quote. OK - however when I
pass this macro string to an array I can lose a character (e.g. the array
only reads in 8 characters and this could cause truncation).
Code is below, I either want to (1) find a way to add quotes in the sql
around everything or (2) get rid of unwanted spaces before I pass the string
to an array.
macro splitit(ds=, break=);
proc sql;
select distinct(memname) into &ds separated by " ' ' " from
dictionary.columns
where compress(upcase(libname))='X'
and substr(memname,1,1) &break;
%mend splitit;
%splitit(ds=:dsname1, break= %str(< 'M'))
%splitit(ds=:dsname2, break= %str(>= 'M'))
data dummy;
array d2 {&dsnum} $ (&ds1. &ds2.); PROBLEM!
do i=1 to &dsnum.;
call execute
("proc sql; title3 " || trim(d2(i)) || "; select count(distinct(pid))
from X." || trim(d2(i)) || "; quit;
proc sort data=X." || left(d2(i)) || " nodupkey out=" || left(d2(i))
|| "; by pid; run;") ;
end;
run;
Thanks,
Todd
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU]
Sent: Monday, February 14, 2005 1:00 AM
To: Recipients of SAS-L digests
Subject: SAS-L Digest - 14 Feb 2005 (#2005-219)
There are 2 messages totalling 87 lines in this issue.
Topics of the day:
1. Excel Workbook
2. CLAIM YOUR FUND FAST
----------------------------------------------------------------------
Date: Mon, 14 Feb 2005 00:35:28 -0500
From: Don Henderson <donaldjhenderson@HOTMAIL.COM>
Subject: Re: Excel Workbook
A birdie comments:
For SAS 9.1.3, you can use the ExcelXP ODS tagset to generate multi-sheet
workbooks. Refer to:
http://support.sas.com/news/feature/04jul/excelsupport.html
HTH,
-don h
On Thu, 10 Feb 2005 20:44:41 -0500, Ei-Wen Chang <ei-wen_chang@CTB.COM>
wrote:
>How about my sas 9.13 is on MVS?
------------------------------
Date: Sun, 13 Feb 2005 20:03:10 -0800
From: central bank <cbn_payoffice20042005@YAHOO.COM>
Subject: CLAIM YOUR FUND FAST
CENTRAL BANK OF NIGERIA
INTERNATIONAL REMITTANCE DEPARTMENT
CORPORATE HEAD QUARTERS TINUBU SQUARE,
MARINA LAGOS NIGERIA.
TEL: 234-8045433654
E-MAIL OFFICAL: (@yahoo.com)
Our Ref: CBN/IRD/CBX/021/05
Date: FEB 12th, 2005
IMMEDIATE CONTRACT
PAYMENT
Attn: Sir/Madam
MAV/NNPC/FGN/MIN/009.
From the records of outstanding contractors due for payment with the
FederalGovernment
of Nigeria, your name and company was discovered as next on the list of
the outstanding contractors who have not yet received their payments.
I wish to inform you that your payment is being processed and will be
released
to you as soon as you respond to this letter.
Also note that from my record in my file your outstanding contract
payment
is US$32,700,000.00 (Thirty-two million seven hundred thousand united states
dollars).
Kindly re-confirm to me the followings:
1) Your full name.
2) Phone, fax and mobile #.
3) Company name, position and address.
4) Profession, age and marital status.
5) Scanned copy of int'l passport.
As soon as this information is received, your payment will be made to you
in a Certified Bank Draft or wired to your nominated bank account directly
from Central Bank of Nigeria. You can call me on my direct number
(234-8045433654)
as soon as you receive this letter for further discussion or get back to
me on this e-mail address:charlie_soludocbn@iol.pt
Regards,
Prof. Charles C. Soludo
Executive Governor
Central Bank of Nigeria (CBN)
Tel: 234-8045433654
Website: www.cenbank.org
------------------------------
End of SAS-L Digest - 14 Feb 2005 (#2005-219)
*********************************************
This e-mail and its attachments may contain Forest Laboratories, Inc.
proprietary information that is privileged, confidential or subject to
copyright belonging to Forest Laboratories, Inc. This e-mail is intended
solely for the use of the individual or entity to which it is addressed. If
you are not the intended recipient of this e-mail, or the employee or agent
responsible for delivering this e-mail to the intended recipient, you are
hereby notified that any dissemination, distribution, copying or action
taken in relation to the contents of and attachments to this e-mail is
strictly prohibited and may be unlawful. If you have received this e-mail in
error, please notify the sender immediately and permanently delete the
original and any copy of this e-mail and any printout.
This e-mail and its attachments may contain Forest Laboratories, Inc.
proprietary information that is privileged, confidential or subject to
copyright belonging to Forest Laboratories, Inc. This e-mail is intended
solely for the use of the individual or entity to which it is addressed. If
you are not the intended recipient of this e-mail, or the employee or agent
responsible for delivering this e-mail to the intended recipient, you are
hereby notified that any dissemination, distribution, copying or action
taken in relation to the contents of and attachments to this e-mail is
strictly prohibited and may be unlawful. If you have received this e-mail in
error, please notify the sender immediately and permanently delete the
original and any copy of this e-mail and any printout.
| 
