Date: Fri, 12 Jul 2002 11:46:09 -0400
Reply-To: Ed Heaton <EdHeaton@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Ed Heaton <EdHeaton@WESTAT.COM>
Subject: Re: query: creating macro variables
Content-Type: text/plain; charset="iso-8859-1"
David Ward is certainly correct. If the subjects were sequentially
numbered, you could use the following code.
Proc sql /*noPrint*/ ;
Select trim(Y) into :subject1-:subject1000000 from x ;
Quit ;
However, this was too much of an assumption for me to make.
Ed
Edward Heaton, Senior Systems Analyst,
Westat (An Employee-Owned Research Corporation),
1550 Research Boulevard, Room 2018, Rockville, MD 20850-3195
Voice: (301) 610-4818 Fax: (301) 294-3992
mailto:EdwardHeaton@westat.com http://www.westat.com
-----Original Message-----
From: David L. Ward [mailto:dward@SASHELP.COM]
Sent: Friday, July 12, 2002 11:04 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: query: creating macro variables
Using proc sql to create macro statements does not seem very efficient (just
a guess) so I performed a little test. I created a 10,000 observation data
set and tried both a data step with call symput and Ed's approach of
creating %let statements in the lets variable. A first observation is that
you're limited to 32767 characters in the macro variable LETS. Here's what
happened:
1 data subj;
2 y='A';
3 do subject=1 to 10000;
4 output;
5 end;
6 run;
NOTE: The data set WORK.SUBJ has 10000 observations and 2 variables.
NOTE: DATA statement used:
real time 0.04 seconds
cpu time 0.04 seconds
7 %let start=%sysfunc(time());
8 data _null_;
9 set subj;
10 call symput('subject'||compress(put(subject,8.)),y);
11 run;
NOTE: There were 10000 observations read from the data set WORK.SUBJ.
NOTE: DATA statement used:
real time 2.21 seconds
cpu time 2.07 seconds
12 %put NOTE: Data step approach took %sysevalf(%sysfunc(time())-&start)s;
NOTE: Data step approach took 2.22300004959834s
13 %let start=%sysfunc(time());
14 Proc sql noPrint ;
15 Select
16 '%let subject' || /* Must use single-quotes here. */
17 trim( left( put(subject,5.) ) ) ||
18 "=" || trim(Y) || ";"
19 into :lets separated by " "
20 from subj
21 ;
22 Quit ;
NOTE: PROCEDURE SQL used:
real time 0.29 seconds
cpu time 0.16 seconds
ERROR: Overflow has occurred; evaluation is terminated.
ERROR: Out of memory.
23 &lets
I'm sure this is probably because the macro variable ran out of "room" and
SQL didn't know how to handle it.
- David W
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU]On Behalf Of Ed
Heaton
Sent: Friday, July 12, 2002 10:42 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: query: creating macro variables
David,
I did forget the separated by clause. Sorry for submitting the untested
code. It should be...
Proc sql /*noPrint*/ ;
Select
'%let subject' || /* Must use single-quotes here. */
trim( left( put(subject,5.) ) ) ||
"=" || trim(Y) || ";"
into :lets separated by " "
from x
;
Quit ;
&lets
Ed
-----Original Message-----
From: David L. Ward [mailto:dward@SASHELP.COM]
Sent: Friday, July 12, 2002 10:13 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: query: creating macro variables
Ed,
Unless I'm missing something your sample code does not create a series of
macro variables, only one macro variable "lets" that is assigned the
expression '%let subjectN=Y;'. Unless there is either a separated by clause
or a list of macro variables (like :subject1-:subjectN), SQL won't be able
to serve up multiple records of subject and Y information. I don't think
that Proc SQL can accomplish what this user wanted?
Regards
David W
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU]On Behalf Of Ed
Heaton
Sent: Friday, July 12, 2002 9:52 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: query: creating macro variables
Don,
The following should do the trick. It creates a series of macro LET
statements and then uses those statements to assign your macro variables.
Data x ;
Input subject Y $ ;
Cards ;
1 A
2 AB
3 ABC
;
Proc sql /*noPrint*/ ;
Select
'%let subject' || /* Must use single-quotes here. */
trim( left( put(subject,5.) ) ) ||
"=" || trim(Y) || ";"
into :lets
from x
;
Quit ;
&lets
Ed
Edward Heaton, Senior Systems Analyst,
Westat (An Employee-Owned Research Corporation),
1550 Research Boulevard, Room 2018, Rockville, MD 20850-3195
Voice: (301) 610-4818 Fax: (301) 294-3992
mailto:EdwardHeaton@westat.com http://www.westat.com
-----Original Message-----
From: Don Aya [mailto:donsas99@HOTMAIL.COM]
Sent: Thursday, July 11, 2002 5:47 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: query: creating macro variables
Hello SAS-Lers;
I have a macro question which I hope you can help.
Suppose I have the following data set:
data x;
input subject Y $;
cards;
1 A
2 AB
3 ABC
;
run
I want to creat macro variables for each subject such that
&subject1 has 'A'
&subject2 has 'AB'
&subject3 has 'ABC'
I highly appreciate your help.
Thanks.
Don
_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com
