topic Re: Create a variable using macro variable in SAS Programming

Create a variable using macro variable

Ujjawal — Tue, 26 Jan 2016 17:23:49 GMT

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!

Re: Create a variable using macro variable

Reeza — Tue, 26 Jan 2016 17:27:39 GMT

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'?

Re: Create a variable using macro variable

Ujjawal — Tue, 26 Jan 2016 17:36:09 GMT

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?

Re: Create a variable using macro variable

Astounding — Tue, 26 Jan 2016 17:39:38 GMT

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.

Re: Create a variable using macro variable

Reeza — Tue, 26 Jan 2016 17:39:55 GMT

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.

Re: Create a variable using macro variable

Reeza — Tue, 26 Jan 2016 17:41:26 GMT

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.

Re: Create a variable using macro variable

Ujjawal — Tue, 26 Jan 2016 18:52:07 GMT

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

Re: Create a variable using macro variable

Astounding — Tue, 26 Jan 2016 19:22:00 GMT

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.

Re: Create a variable using macro variable

Ujjawal — Tue, 26 Jan 2016 21:03:16 GMT

Thanks a ton!