Hello,
I have a data set that contain list of variables for each population.
I want to perform a dynamic sas code that for each population will create list of variables in a macro variable.
Each macro variable name should reflect the population .
The macro variables will contain the vars values with comma between values.
Expected results are :
Vector_Vars1=X Y Z;
Vector_Vars1=X2 Y W;
Vector_Vars3=X Y R T Q;
Unfortunately I get macro variables with no value
25 GOPTIONS ACCESSIBLE;
WARNING: Apparent symbolic reference VECTOR_VARS1 not resolved.
26 %put &Vector_Vars1;
&Vector_Vars1
27 %put &Vector_Vars2;
WARNING: Apparent symbolic reference VECTOR_VARS2 not resolved.
&Vector_Vars2
28 %put &Vector_Vars3;
WARNING: Apparent symbolic reference VECTOR_VARS3 not resolved.
&Vector_Vars3
Here is the code I run
Data vars_list;
input population var_name $;
cards;
1 X
1 Y
1 Z
2 X2
2 Y
2 W
3 X
3 Y
3 R
3 T
3 Q
;
Run;
proc sql noprint;
select distinct population into : Vector_population SEPARATED by '+'
from vars_list
;
quit;
%put &Vector_population;
%let n_Vector_population = %sysfunc(countw(&Vector_population));
%put &n_Vector_population;
%macro RRR;
%do j=1 %to &n_Vector_population.;
%let pop=%scan(&Vector_population.,&j.,+);
proc sql noprint;
select distinct VAR_name into : Vector_Vars&pop. SEPARATED by ' '
from vars_list
where population=&pop.
;
quit;
%end;
%mend RRR;
%RRR
%put &Vector_Vars1;
%put &Vector_Vars2;
%put &Vector_Vars3;
You didn't create a global macro variable, only local. Add a GLOBAL statement to have it work.
That being said, a data step is more efficient.
proc sql noprint; select distinct population into : Vector_population SEPARATED by '+' from vars_list ; quit; %put &Vector_population; %let n_Vector_population = %sysfunc(countw(&Vector_population)); %put &n_Vector_population; %macro RRR; %do j=1 %to &n_Vector_population.; %let pop=%scan(&Vector_population.,&j.,+); %global Vector_vars&pop.; proc sql noprint; select distinct VAR_name into : Vector_Vars&pop. SEPARATED by ' ' from vars_list where population=&pop. ; quit; %end; %mend RRR; %RRR %put &Vector_Vars1; %put &Vector_Vars2; %put &Vector_Vars3;
No macro needed, no looping needed, and now the macro variables created are global.
proc transpose data=vars_list out=vars_list_t;
by population;
var var_name;
run;
data vars_list_t1;
length all_cols $ 200;
set vars_list_t;
all_cols=catx(' ',of col:);
keep all_cols population;
run;
proc sql noprint;
select all_cols into :vectorvars1- from vars_list_t1 order by population;
quit;
%put &=vectorvars1;
%put &=vectorvars2;
%put &=vectorvars3;
It is great but in the real world I need to concatenate 100 variables and I get warning
WARNING: In a call to the CATX function, the buffer allocated for the result was not long enough to contain the concatenation of
all the arguments. The correct result would contain 1326 characters, but the actual result might either be truncated to
1000 character(s) or be completely blank, depending on the calling environment. The following note indicates the left-most
argument that caused truncation.
Which length did you use in the LENGTH statement?
You could also do this using CALL SYMPUTX in a DATA step with BY-GROUP processing. e.g.
data _null_ ;
set vars_list ;
by population ;
length varlist $200;
retain varlist ;
if first.population then varlist=var_name ;
else varlist=cats(" ",varlist,var_name) ;
if last.population then call symputx(cats("Vector_Vars",population),varlist) ;
run ;
%put _user_ ;
Or fewer statements, but less readable to my eye, below avoids creating the ancillary variable VARLIST by executing CALL SYMPUTX once per record and using symget to resolve the macro variable during execution-time:
data _null_ ;
set vars_list ;
by population ;
if first.population then call symputx(cats("Vector_Vars",population),var_name) ;
else call symputx(cats("Vector_Vars",population)
,catx(" ",symget(cats("Vector_Vars",population)),var_name)
);
run ;
%put _user_ ;
Or just use CALL EXECUTE() to generate a series of %LET statements.
62 data _null_ ; 63 set vars_list ; 64 by population ; 65 mvar=cats("Vector_Vars",population); 66 if first.population then call execute(catx(' ','%let',mvar,'=;')); 67 call execute(catx(' ','%let',mvar,cats('=&',mvar),var_name,';')); 68 run ; NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds NOTE: There were 11 observations read from the data set WORK.VARS_LIST. NOTE: CALL EXECUTE routine executed successfully, but no SAS statements were generated. 69 %put _user_ ; GLOBAL VECTOR_VARS1 X Y Z GLOBAL VECTOR_VARS2 X2 Y W GLOBAL VECTOR_VARS3 X Y R T Q
You didn't create a global macro variable, only local. Add a GLOBAL statement to have it work.
That being said, a data step is more efficient.
proc sql noprint; select distinct population into : Vector_population SEPARATED by '+' from vars_list ; quit; %put &Vector_population; %let n_Vector_population = %sysfunc(countw(&Vector_population)); %put &n_Vector_population; %macro RRR; %do j=1 %to &n_Vector_population.; %let pop=%scan(&Vector_population.,&j.,+); %global Vector_vars&pop.; proc sql noprint; select distinct VAR_name into : Vector_Vars&pop. SEPARATED by ' ' from vars_list where population=&pop. ; quit; %end; %mend RRR; %RRR %put &Vector_Vars1; %put &Vector_Vars2; %put &Vector_Vars3;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.