topic Re: Proc SQL into macro variables in SAS Procedures

Proc SQL into macro variables

steve_citi — Mon, 14 Jun 2010 21:31:02 GMT

I have a dataset with 10 observations which has only 2 variables, division and manager.

I would like to create macro variable &division1 through &division10 and &manager1 through &manager10, such that &division1 and &manager1 come from the same row.

The following code generates the division macros variables correctly, however the manager macro variables are not the correct ones.

PROC SQL noprint;
select count (distinct division) into :n from delivery_division_info;
select distinct division into :division1 through :division%left(&n)
from delivery_division_info;
select distinct manager into :manager1 through :manager%left(&n)
from delivery_division_info;
quit;

Re: Proc SQL into macro variables

Cynthia_sas — Mon, 14 Jun 2010 21:54:50 GMT

Hi:
In this program, I want to get every student from SASHELP.CLASS and their age into numbered macro variables. If I don't have AGE on the same SELECT as NAME, there's no guarantee that the names and ages will "match up" -- they might get selected the same way both times, but they might not. The SELECT statement can be optimized to get data and with 2 separate SELECTs, you -could- potentially get the 2 queries returned in 2 different orders. Also, if you do a "select distinct manager", what happens if you have 1 manager for 2 departments?? He or she will show up in the macro variables only 1 time, but don't you want him or her to be in the macro variables twice -- one time for each department row???

cynthia
[pre]
ods listing;
PROC SQL;
select count (name) into :n from sashelp.class;
select name, age into :name1 through :name%left(&n),
:age1 through :age%left(&n)
from sashelp.class;
quit;

** partial display of macro variable values;
%put count= &n;
%put name1 and age1 = &name1 &age1;
%put name2 and age2 = &name2 &age2;
%put name3 and age3 = &name3 &age3;
%put name4 and age4 = &name4 &age4;
%put name5 and age5 = &name5 &age5;
%put name6 and age6 = &name6 &age6;
%put name7 and age7 = &name7 &age7;
%put name8 and age8 = &name8 &age8;
[/pre]

Re: Proc SQL into macro variables

AUTigers — Tue, 15 Jun 2010 03:55:20 GMT

Hi,Steve,

if your sas is running on Windows, you can use automatic macro variable SYSMaxLong to create mvars without knowing how many observations in your dataset.

proc sql;
select sex,age into
:sex1-:sex&SYSMAXLONG ,:age1-:age&SYSMAXLONG
from sashelp.class;
quit;

%put _user_;

in this case, you will see 19 sex mvars and age mvars in the log.

HTH

Re: Proc SQL into macro variables

Ksharp — Tue, 15 Jun 2010 06:22:56 GMT

Hi. There is a auto macro variable (&sqlobs) created by proc sql , you can use it.

[pre]
proc sql;
select division,manager
into :division1 - :division&sqlobs , :manager1 - :manager&sqlobs
from delivery_division_info;
[/pre]

Ksharp

Re: Proc SQL into macro variables

Peter_C — Tue, 15 Jun 2010 15:46:44 GMT

> Hi. There is a auto macro variable (&sqlobs) created
> by proc sql , you can use it.
>
>
> [pre]
> proc sql;
> select division,manager
> into :division1 - :division&sqlobs , :manager1 -
> :manager&sqlobs
> from delivery_division_info;
> [/pre]
>
>
> Ksharp

have you tried this?
It gives me these warnings[pre]WARNING: INTO Clause :division1 through :division0 does not specify a valid sequence of macro variables.
WARNING: INTO Clause :manager1 through :manager0 does not specify a valid sequence of macro variables.[/pre]
Normally I would just use a large number (I really don't want a really large number of macro variables), so I just use 9999 or even 999, in[pre]proc sql;
select division,manager
into :division1 - :division999
, :manager1 - :manager999
from delivery_division_info ;
quit ;[/pre]

Re: Proc SQL into macro variables

Ksharp — Wed, 16 Jun 2010 07:40:06 GMT

Hi. Peter you are right .It should have a same sql statement ahead.
try this.

[pre]
options symbolgen;
proc sql;
select name
from sashelp.class;

select name
into :name1 - :name&sqlobs
from sashelp.class;
quit;
%put &name1 &name2 &&name&sqlobs.;
%put _user_;
[/pre]

Ksharp

Re: Proc SQL into macro variables

Patrick — Wed, 16 Jun 2010 08:51:13 GMT

Or instead of Proc SQL a datastep with only one pass through the data:

data _null_;
set sashelp.class nobs=nobs;
if _n_=1 then call symput('N_pairs',cats(nobs));
call symput(cats('name',_n_),cats(Name));
call symput(cats('Age',_n_),cats(Age));
run;

%macro listvars;
%do i=1 %to &N_pairs;
%put;
%put name&i= &&&name&i;
%put age&i= &&&age&i;
%end;
%mend;

%listvars

Re: Proc SQL into macro variables

Flip — Wed, 16 Jun 2010 12:10:11 GMT

Patrick;
I am going to point this out because I have seen this in several macros at my current location, and it drives me nuts.

%put name&i= &&&name&i; will work only if you are aware of everything in your current environment. It should be
%put name&i= &&name&i;

Why;
&& -- resolves to &
&name -- if it has not been assigned (and you are assuming it has not) it resolves to "name" inside a complex macro variable like this. If there was a global macro variable set it resolves to that.
&i -- resolves to your counter.

So if somewhere in your job stream there is a %let name = fred, your log will state "&fred1 not resolved".

If you are making macros available to others, this can cause problems.

Re: Proc SQL into macro variables

Patrick — Fri, 18 Jun 2010 13:10:47 GMT

Flip

You're of course right - and I must have been tired....

Cheers
Patrick

Re: Proc SQL into macro variables

data_null__ — Fri, 18 Jun 2010 17:13:26 GMT

> Patrick;
> I am going to point this out because I have seen this
> in several macros at my current location, and it
> drives me nuts.

I would be equally concerned about any macro/program that uses this "macro array" technique. Why would anyone want to take perfectly good data from a data set and put it into a macro array or TWO arrays as in this example? I guess the next question how to put ALL data set variables into macro arrays.

Macro arrays are useless. If you're putting that much data into macro variables you need to rewrite the program.

Re: Proc SQL into macro variables

Peter_C — Fri, 18 Jun 2010 19:20:01 GMT

how about another approach (than rewriting)
how about more training