BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Ronein
Meteorite | Level 14

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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;

View solution in original post

7 REPLIES 7
Astounding
PROC Star
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
Diamond | Level 26

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;

 

--
Paige Miller
Ronein
Meteorite | Level 14

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.
Quentin
Super User

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_ ;
BASUG is hosting free webinars Next up: Mike Sale presenting Data Warehousing with SAS April 10 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Tom
Super User Tom
Super 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
Reeza
Super User

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;

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

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.

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
  • 7 replies
  • 543 views
  • 4 likes
  • 7 in conversation