Date: Mon, 14 Feb 2005 17:22:26 +0000
Reply-To: toby dunn <tobydunn@HOTMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: toby dunn <tobydunn@HOTMAIL.COM>
Subject: Re: Remove unwanted spaces from macro variable
In-Reply-To: <5604C2B45493FB40B30AE84F55529FFB0460DBAC@mail-nyc1.frxntnyc.frx2.com>
Content-Type: text/plain; format=flowed
Todd,
I have no clue as to how you got a quoted list from my code after debugging
it and then testing it trying to get a set of datasets with no duplicate
values of 'x' from a list of datasets in my work library my program looked
like:
Data m_1 ;
x = 1 ;
run ;
data m_2 ;
x = 1 ;
run ;
data m_3 ;
x = 1 ;
run ;
data z_1;
z = 1;
run ;
%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 &var ;
run ;
%end ;
%put <<<&ds_list>>> ;
%mend distinct ;
%distinct(lib= work , mem= %quote(=* 'M') , var=x)
My log looked like:
1 data m_1 ;
2 x = 1 ;
3 run ;
NOTE: The data set WORK.M_1 has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
3 !
4
5 data m_2 ;
6 x = 1 ;
7 run ;
NOTE: The data set WORK.M_2 has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
7 !
8
9 data m_3 ;
10 x = 1 ;
11 run ;
NOTE: The data set WORK.M_3 has 1 observations and 1 variables.
2 The SAS System
11:14 Monday, February 14, 2005
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
11 !
12
13 data z_1;
14 z = 1;
15 run ;
NOTE: The data set WORK.Z_1 has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
15 !
16
17 %macro distinct(lib = , mem = , var= ) ;
18
19 %local i stop ds_list ;
20
21
22
23 proc sql noprint ;
24
25 select memname into : ds_list separated by " "
26
27 from dictionary.tables
28
29 where libname = %upcase("&lib") and memname
%upcase(%unquote(&mem)) ;
30
31
32
33 %let stop = &sqlobs ;
34
35
36
37 quit ;
38
39
40
41 %do i = 1 %to &stop ;
42
43
44
45 proc sort
46
47 data = %scan(&ds_list,&i) nodupkey
48
49 out = new_%scan(&ds_list,&i) ;
50
51 by &var ;
52
53 run ;
54
55
3 The SAS System
11:14 Monday, February 14, 2005
56
57 %end ;
58
59 %put <<<&ds_list>>> ;
60
61 %mend distinct ;
62
63
64
65 %distinct(lib= work , mem= %quote(=* 'M') , var=x)
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
NOTE: There were 1 observations read from the data set WORK.M_1.
NOTE: 0 observations with duplicate key values were deleted.
NOTE: The data set WORK.NEW_M_1 has 1 observations and 1 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds
NOTE: There were 1 observations read from the data set WORK.M_2.
NOTE: 0 observations with duplicate key values were deleted.
NOTE: The data set WORK.NEW_M_2 has 1 observations and 1 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
NOTE: There were 1 observations read from the data set WORK.M_3.
NOTE: 0 observations with duplicate key values were deleted.
NOTE: The data set WORK.NEW_M_3 has 1 observations and 1 variables.
NOTE: PROCEDURE SORT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds
<<<M_1 M_2 M_3>>>
66
NOTE: SAS Institute Inc., SAS Campus Drive, Cary, NC USA 27513-2414
NOTE: The SAS System used:
real time 0.04 seconds
cpu time 0.03 seconds
I think it does what you are looking.
Toby Dunn
From: "Case,Todd" <Todd.Case@FRX.COM>
Reply-To: "Case,Todd" <Todd.Case@FRX.COM>
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Remove unwanted spaces from macro variable
Date: Mon, 14 Feb 2005 11:51:51 -0500
I really like your approach... BUT it still doesn't address the problem,
even if this approach is used there is still the problem of unwanted
NOTE (and possible problem in future SAS release):
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.
So the following issue still needs to be addressed:
I have the following macro variable, the goal is to remove the spaces
between the character strings (actually dataset names) and the quotes,
these macro strings will later be passed into array statements and in
reality the x...'s will be dataset names which could be truncated if
there are unwanted spaces:
SYMBOLGEN: Macro variable DS_LIST resolves to 'x1 ' ' x2 ' ' x3 ' ' x4
' ' x5 ' ' x6 ' ' x7 ' ' x8 ' 'x9 ' ' x10 ' ' x11 ' ' x12 ' ' x13 ' '
x14 ' ' x15 ' ' x16' ...
Any ideas?
Thanks!
Todd
SYMBOLGEN: Macro variable DS1 resolves to 'x1 ' ' x2 ' ' x3 ' ' x4 ' '
x5 ' ' x6 ' ' x7 ' ' x8 ' 'x9 ' ' x10 ' ' x11 ' ' x12 ' ' x13 ' ' x14 '
' x15 ' ' x16'
SYMBOLGEN: Macro variable DS2 resolves to 'x17 ' ' x18 ' ' x19 ' ' x20
' ' x21 ' ' x22 ' ' x23 ' ' x24 ' ' x25 ' 'x25 ' ' x26 ' ' x27 ' ' x28 '
' x29 ' ' x30 ' ' x31 ' ' x32 ' ' x33'
I still need to remove the spaces as follows:
-----Original Message-----
From: toby dunn [mailto:tobydunn@hotmail.com]
Sent: Monday, February 14, 2005 10:02 AM
To: Case,Todd; SAS-L@LISTSERV.UGA.EDU
Subject: Re: Remove unwanted spaces from macro variable
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.
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.
