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)
select distinct manager into :manager1 through :manager%left(&n)
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???
select count (name) into :n from sashelp.class;
select name, age into :name1 through :name%left(&n),
:age1 through :age%left(&n)
** 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;
> Hi. There is a auto macro variable (&sqlobs) created
> by proc sql , you can use it.
> proc sql;
> select division,manager
> into :division1 - :division&sqlobs , :manager1 -
> from delivery_division_info;
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;
into :division1 - :division999
, :manager1 - :manager999
from delivery_division_info ;
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;
&& -- 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.
> 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.