Date:         Fri, 12 Jul 2002 11:04:13 -0400
Reply-To:     "David L. Ward" <dward@SASHELP.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "David L. Ward" <dward@SASHELP.COM>
Subject:      Re: query: creating macro variables
In-Reply-To:  <08B08C9FA5EBD311A2CC009027D5BF8104612795@remailnt2-re01.westat.com>
Content-Type: text/plain; charset="iso-8859-1"

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