topic Re: macro write values into a variable instead of create new variables in SAS Programming

macro write values into a variable instead of create new variables

Vi_ — Fri, 20 Apr 2018 19:23:20 GMT

Hi All,

Background:

I need a macro to create variable name list.

I am using SAS Enterprise Guide 7.1 on Windows

Issue:

I have issues writing macro variables into a single field.

Here is an example:

data example ;
input Name $ 8.;
Datalines;
Jake Lin
Mike Lee
Jim Jo
Max Kim
;
run;

Before create macro variable name list, I need to transform the space into underscore. My macro as follow:

%macro getNmList;
		%let namelist=%sysfunc(open(example));
		%let NObs=%sysfunc(attrn(&namelist,NOBS));
		%syscall set(namelist);

			Data TransformedName;

			set example;
			
			%do t=1 %to &NObs;
				%let rc=%sysfunc(fetchobs(&namelist,&t));
				%let Newname=%sysfunc(tranwrd(&name,%str( ),%str(_) ));
				%put &Newname;				

			TranName=&Newname;
			%end;
			run;


		%let namelist=sysfunc(close( namelist ));

%mend;
%getNmList

The %put &Newname statement successfully returned my desired output values ( change space to underscore) :

Jake_Lin
Mike_Lee
Jim_Jo
Max_Kim

However, the output table is not what I expected. The log and output result as follow:

(Node: I also notice the datatype for TranName is incorrect but I am not sure why)

I would like the result shown as :

Attempts:

i). I suspect the error happened in this statement : TranName=&Newname; Thus, I tried using TranName=SYMGET('Newname');

But it only returned the last value, and thus, the result TransName field are all Max_Kim.

ii). I also tried to re-write the macro using call symputx as follows:

data _null_;

  set example ;

  call symputx(cats('origname',strip(_n_)),Name);

run;

%put &origname1 &origname2 &origname3 &origname4;

/****Log output : Jake Lin Mike Lee Jim Jo Max Kim**********/

%macro transpace;

data _null_;

%do i=1 %to 4;

%let tranName&i =tranwrd(('origname&i'),%str( ),%str(_));

%end;

run;
%mend;

%transpace

%put &tranName1;

/*******Log CANNT get &tranName1 and I having 

WARNING: Apparent symbolic reference TRANNAME1 not resolved.*********/

After all the attempts, I am not sure how can I get to my desire name list~~~please help~~~~

Thank you in advance.

Re: macro write values into a variable instead of create new variables

PaigeMiller — Fri, 20 Apr 2018 19:26:45 GMT

tranname="&newname";

But you don't need macros here, this can be done entirely in a DATA step using the TRANSLATE function.

Re: macro write values into a variable instead of create new variables

Reeza — Fri, 20 Apr 2018 20:17:43 GMT

Ok...here's another way, using SQL. I assume you wanted a quoted delimited list, but if you don't remove the QUOTE() function and you can modify the separated by section.

data have;
name='Jack Johnson'; output;
name = 'Green Goblin'; output;
name = 'Bruce Wayne'; output;
run;

proc sql noprint;
select quote(translate(strip(name), "_", " ")) into : name_list separated by ", "
from have;
quit;

%put &name_list;

If you want multiple macro variables:


proc sql noprint;
select quote(translate(strip(name), "_", " ")) into : name1- 
from have;
quit;



%put &name1.;
%put &name2.;
%put &name3.;

Re: macro write values into a variable instead of create new variables

Vi_ — Fri, 20 Apr 2018 20:22:40 GMT

Hi Paige,

Thank you for fast response. However, changing the code to tranname="&newname" does not solve my problem because the output table is not what I want. It only returned the last value:

i understand that I can possibly change this in a DATA step. But in my case, if I do it in DATA step, I might need to change my code more than having this macro to run.

Thank you,

Re: macro write values into a variable instead of create new variables

Reeza — Fri, 20 Apr 2018 20:27:49 GMT

You just want the varaible in the data set that way?

Then use a query with a computed column that is:

translate(strip(name), "_", " ")

or:

Re: macro write values into a variable instead of create new variables

SuryaKiran — Fri, 20 Apr 2018 20:33:27 GMT

If your final goal is to put all the values into a macro then try using Proc SQL with INTO:

data example ;
input Name $ 8.;
Datalines;
Jake Lin
Mike Lee
Jim Jo
Max Kim
;
run;

PROC SQl;
select Name INTO: Names1 separated by ','
from example
;
quit;
proc sql;
select translate(strip(name), "_", " ") into: Names2 separated by " "
from example;
quit;
%put &Names1;
%put &names2;

Re: macro write values into a variable instead of create new variables

ballardw — Fri, 20 Apr 2018 21:52:39 GMT

@Vi_ wrote:

Hi Paige,

Thank you for fast response. However, changing the code to tranname="&newname" does not solve my problem because the output table is not what I want. It only returned the last value:

i understand that I can possibly change this in a DATA step. But in my case, if I do it in DATA step, I might need to change my code more than having this macro to run.

Maybe because @PaigeMiller did not provide complete code you did not understand his recommendation.

Please see

data transformedname;
   set example;
   tranname = translate(strip(name),'_',' ');
run;

for a non-macro approach to generate what I think is the desired output.

The potential for issues with macro variable scope and repeated requirements to iterate through the value list based on the value of a variable in your data set sounds like the start of a nightmare.

Re: macro write values into a variable instead of create new variables

Tom — Fri, 20 Apr 2018 22:45:42 GMT

To get better help you need to explain more clearly what you want.

I need a macro to create variable name list.

Do you want the list in a macro variable? So perhaps something like this:

%let namelist=Jake_Lin Mike_Lee Jim_Jo Max_Kim ;

If so there is no need to create a macro to do that, a simple SQL query will do.

proc sql noprint;
select translate(strip(name),'_',' '))
  into :namelist separated by ' '
  from example
;
quit;

The macro code you posted seems to be confused about what happens in macro code and what happens in SAS code.

If you really wanted to create the list in a dataset variable then use a data step for that.

data TransformedName;
  length TranName $400 ;
  retain tranname;
  set example end=eof ;
  tranname=catx(' ',tranname,translate(strip(name),'_',' '));
  if eof;
  keep tranname;
run;

Re: macro write values into a variable instead of create new variables

Kurt_Bremser — Sat, 21 Apr 2018 09:30:19 GMT

You have the typical misunderstanding of the macro processor.

The macro processor is a CODE GENERATOR, not a tool for data manipulation. It works BEFORE(!!!) any base SAS code starts to execute.

So the macro creates this data step code:

Data TransformedName;
set example;
TranName=Jake_Lin;
TranName=Mike_Lee;
TranName=Jim_Jo;
TranName=Max_Kim;
run;

You see you have no strings to the right, but something SAS sees as variable names, and the data step compiler dutifully creates them (as they are not on your input dataset), using the default type numeric, and they stay uninitialized (see the NOTEs). Since the right side of your assignment is numeric, tranname is also created numeric.

Even if those variables were present, only the last assignment would take effect before the observation is written out.

What you want is extremely easily accomplished with a simple call of the translate() function, no macro needed AT ALL. See Maxim 11.

Attempt i) failed because the data step only sees the last value assigned to &newname.

Use @ballardw's code and carefully study the second sentence of my post.

Re: macro write values into a variable instead of create new variables

Vi_ — Sat, 21 Apr 2018 14:16:29 GMT

Reeza:

Thanks for sharing two ways to help me approach my goal. Both of them work for me!!

Ballardw:

Thank you for providing the full code and point out some potential issues

Suryakiran:

You code is very easy to understand. Thanks!!

KurtBremser:

Thank you for answer the other issue I have and pointing out my misunderstand or unclear part of sas macro. I somehow assuming sas macro is the same as other programming languages that can easily write a for loop to iterate my data. But it seems like macro is a bit different than what I thought it is.

Tom:

In short, I wrote a maco to automatically create dummy variables (using proc transpose) and I used Proc sql namelist to get the dummy variable names and put it into Proc Logistic Model statement. However, It did not work because all the dummy variable name were cut off when proc sql namelist macro meets space. Eventually, the macro namelist returns partial variable name, thus, my logistic model cannot read it correctly.

I think in the end the solution to my problem is to change the variable name before creating dummies. I appreciate all the responses. Thank you all for giving me a macro class. I learned a lot from this post!!

Re: macro write values into a variable instead of create new variables

Vi_ — Sat, 21 Apr 2018 14:21:15 GMT

Since Reeza provides the solution to my desired output the first, I will select his post as the solution to this post.

Re: macro write values into a variable instead of create new variables

Tom — Sat, 21 Apr 2018 15:12:23 GMT

Not sure why you are making your own dummy variables. PROC LOGISTIC does support the CLASS statement.

If you just want to make a list of variables in SAS code then just use a variable list. If you name the variable using numeric suffixes then you can use

model x=dummy1-dummy2000;

If you name them all with a unique prefix you can use

model x=dummy: ;

If you know their order in the data set you can use a positional list

model x=first_dummy -- last_dummy ;

Also macro code is not the only way to generate SAS code. If you have a list of variable names in a SAS dataset and you want to use it to generate SAS statements then use a data step to make a file that contains the statement(s) and use %INCLUDE to place it where you need it in your program.

filename model temp;
data _null_;
  set varlist end=eof;
  file model lrecl=75 ;
  if _n_=1 then put 'MODEL x=';
  put varname @;
  if eof then put / ';' ;
run;

proc logistic .... ;
%include model;
...

Re: macro write values into a variable instead of create new variables

Vi_ — Mon, 23 Apr 2018 18:03:41 GMT

Hi Tom,

So I did have my reason creating those dummies but thanks for mentioning the CLASS statement.

I am using the unique prefix method because my variable name list varied from model to model. What I did is create separate datasets to store the variable names and for each logistic regression model, macro fetch the variable names from specific namelist dataset.

I have a question about lrecl=75. Please correct me if I misunderstand.

In the example you provide, lrecl=75 means that the system can write the external file with maximum 75 bytes. My problem goes to how do I know how much to specify ?

  file model lrecl=75 ;

Thanks!!

Re: macro write values into a variable instead of create new variables

Tom — Mon, 23 Apr 2018 18:30:12 GMT

The maximum length of a variable name in SAS is 32 characters.

SAS doesn’t really care what line length you use for the Include file.

But the maximum line length that a human can reasonably read/scan is about 75 characters. That is why I added the LRECL=75 option. Code with lines longer than that is a real pain in the *** to read and edit.

Re: macro write values into a variable instead of create new variables

Kurt_Bremser — Tue, 24 Apr 2018 06:37:02 GMT

Also see Maxim 12.