BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ujjawal
Quartz | Level 8

I am trying to create a variable in a new data set using a macro variable. I am trying to store names of numeric variable in a macro variable and then populate names in a variable in a new data set. I know it can be easily done with PROC CONTENTS. It's for my learning. I am not working on a real life project.

 

%macro test;
proc sql noprint;
select name into: nvar separated by " "
from dictionary.columns
where LIBNAME = "SASHELP"
and MEMNAME = "CLASS"
and type = "num";
quit;

 

%let varn=%sysfunc(countw(&nvar%str( )));
%DO i=1 %TO &varn.;
%let varName = %qscan(%sysfunc(compbl(&nvar)),&i,%str( ));
%put &varName;

 

data want;
var1 = symget(&varName.);
output;
run;
%end;

%mend;
%test;

 

Thanks in anticipation. Any help would be highly appreciated!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

Getting much closer to a solution, though.  Picture this DATA step:

 

 

data want;
var1 = symget('varName');
output;
var1 = symget('varName');
output;
var1 = symget('varName');
output;
run;

 

That's actually what your DATA step looks like.  Macro language isn't executing its statements in the middle of your DATA step.  Rather, it is helping generate the statements that become part of your DATA step.  Here's one way that you could change the outcome:

 

data want;

length var1 $ 32;


%do i=1 %TO &cntvar.;
%let varName =%scan(&nvar,&i);
  var1 = "&varName";
  output;
%end;

run;

 

Now &VARNAME will change each time through the loop, and the resulting DATA step code will change along with it.

View solution in original post

8 REPLIES 8
Reeza
Super User

If it's an exercise...think about where the loop needs to actually be. If you have X macro variables does the loop need to be outside the data step or inside. And do you actually need a macro loop or can you go towards a data step or is that not what you want to 'practice'?

Ujjawal
Quartz | Level 8
Thank you for hints. It's not a college assignment. I am learning the concepts on my own. I invested a good amount of time accomplishing this task with PROC SQL SELECT INTO macro variable. I know this kind of situation can be handled with CALL SYMPUT - call symput('nvars' || strip(_n_), name) and then use LOOP and SYMGET in data step. I wanted to know if this can be done with SELECT INTO macro variable?
Reeza
Super User

Select INTO is how you create your variables.

 

I'm referring to the loop where you're trying to assign the values to a variable. 

It doesn't make sense to have that loop outside the data step since you're assigning variabes. Your %do loop should be in the data step.

 

Why not assign the macro variables to a variable and parse it out with SCAN?

Or assign it to a temporary array and then use the array loop to output. 

Or you can use a %do loop within the data step.

 

I think trying to assign the values looping a data step over and over is inefficient and not worth learning. 

Reeza
Super User

I think the point also is, if you're truly trying to learn, having someone provide the solution isn't going to help as much as if you find the answer yourself. 

Astounding
PROC Star

Since you're doing this as an exercise and learning experience, let me give you a few general tips.

 

Pick meaningful names that are easy to interpret.  There is no excuse for naming one macro variable NVAR and another VARN.  That combination of names are too difficult to differentiate.

 

Names that used only within a macro should be defined with a %LOCAL statement:

 

%local i nvar varn varname;

 

Selecting out one name from a list can be simplified:

 

%let varName = %qscan(&nvar,&i,%str( ));

 

When a blank as your delimiter, %QSCAN does not need to compress consecutive blanks.

 

When SYMGET creates a variable in a SAS data set, the length of that variable will be $200.  You might want to define a shorter length first.

 

It's not clear what your final objective is.  If AGE is one of the variables in SASHELP.CLASS, which of these do you want to generate:

 

age = .;

var1 = 'age';

 

If it's the second one, you might want to consider skipping the macro language entirely:

 

proc sql noprint;
create table want as select name as var1
from dictionary.columns
where LIBNAME = "SASHELP"
and MEMNAME = "CLASS"
and type = "num";
quit;

 

Sorry if this sounds harsh.  It's not intended to be ... just a stream of my reactions to the programming techniques.  If you clarify the final objective, we can look at tailoring the second half of the macro.

 

Good luck.

Ujjawal
Quartz | Level 8

Thank for your reply. I have corrected my earlier code. However, loop seems to be not working right. It is returning same value in all the 3 observations. 

 

%macro test;
proc sql noprint;
select name into: nvar separated by " "
from dictionary.columns
where LIBNAME = "SASHELP"
and MEMNAME = "CLASS"
and type = "num";
quit;

%let cntvar = %sysfunc(countw(&nvar));

data want;
%do i=1 %TO &cntvar.;
%let varName =%scan(&nvar,&i);
%put &varName;
var1 = symget('varName');
output;
%end;
run;
%mend;

%test;

The output should look like below (Only 1 variable and 3 observations) -

var1
Age
Height 

Weight

Astounding
PROC Star

Getting much closer to a solution, though.  Picture this DATA step:

 

 

data want;
var1 = symget('varName');
output;
var1 = symget('varName');
output;
var1 = symget('varName');
output;
run;

 

That's actually what your DATA step looks like.  Macro language isn't executing its statements in the middle of your DATA step.  Rather, it is helping generate the statements that become part of your DATA step.  Here's one way that you could change the outcome:

 

data want;

length var1 $ 32;


%do i=1 %TO &cntvar.;
%let varName =%scan(&nvar,&i);
  var1 = "&varName";
  output;
%end;

run;

 

Now &VARNAME will change each time through the loop, and the resulting DATA step code will change along with it.

Ujjawal
Quartz | Level 8
Thanks a ton!

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!

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
  • 8 replies
  • 2079 views
  • 3 likes
  • 3 in conversation