create macro variables with list of vars for each pop

[Ronein]

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;

[Astounding]

Inside a macro definition, SQL creates %local macro variables. But you need those macro variables to be global instead of local, so they still exist after the macro finishes executing.

Add a statement after the %do statement and before the subsequent proc SQL statement:

%global vector_vars&j;

It appears to be the proper name that needs to persist once your macro finishes executing.

[PaigeMiller]

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;

[Ronein]

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.

[Kurt_Bremser]

Which length did you use in the LENGTH statement?

[Quentin]

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_ ;

[Tom]

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

[Reeza · Accepted solution]

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;