BookmarkSubscribeRSS Feed
steve_citi
Calcite | Level 5
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;
10 REPLIES 10
Cynthia_sas
SAS Super FREQ
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]
AUTigers
Calcite | Level 5
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
Ksharp
Super User
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
Peter_C
Rhodochrosite | Level 12
> 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]
Ksharp
Super User
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
Patrick
Opal | Level 21
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
Flip
Fluorite | Level 6
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.
Patrick
Opal | Level 21
Flip

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

Cheers
Patrick
data_null__
Jade | Level 19
> 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.
Peter_C
Rhodochrosite | Level 12
how about another approach (than rewriting)
how about more training

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 2931 views
  • 0 likes
  • 8 in conversation