macro write values into a variable instead of create new variables

Accepted Solution Solved
Reply
Occasional Contributor Vi_
Occasional Contributor
Posts: 10
Accepted Solution

macro write values into a variable instead of create new variables

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:sas log.PNG

 

sas results.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

 

I would like the result shown as : 

Wanted result.PNG

 

 

 

 

 

 

 

 

 

 

 

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.

 

Vi


Accepted Solutions
Solution
‎04-21-2018 10:21 AM
Super User
Posts: 23,771

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

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,054

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

[ Edited ]
tranname="&newname";

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

--
Paige Miller
Occasional Contributor Vi_
Occasional Contributor
Posts: 10

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

Posted in reply to PaigeMiller

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:

01result.PNG

 

 

 

 

 

 

 

 

 

 

 

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,

 

Vi

 

 

 

Super User
Posts: 23,771

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

[ Edited ]

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:

 

 

Super User
Posts: 13,583

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

[ Edited ]

@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:

01result.PNG

 

 

 

 

 

 

 

 

 

 

 

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.

Solution
‎04-21-2018 10:21 AM
Super User
Posts: 23,771

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

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.;
Valued Guide
Posts: 597

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

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;
Thanks,
Suryakiran
Super User
Super User
Posts: 8,118

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

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;

 

Super User
Posts: 10,278

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor Vi_
Occasional Contributor
Posts: 10

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

Posted in reply to KurtBremser

Reeza:

 

 

 

 

Tom: 

I think in the end the solution to my problem is to change the variable name before creating dummies. I appreciate all the responses. 

 

 

 

 

 

 

 

 

 

Super User
Super User
Posts: 8,118

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

[ Edited ]

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

 

Occasional Contributor Vi_
Occasional Contributor
Posts: 10

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

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!!

 

Vi

 

 

Super User
Super User
Posts: 8,118

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

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.

 

Super User
Posts: 10,278

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

Also see Maxim 12.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor Vi_
Occasional Contributor
Posts: 10

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

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 359 views
  • 8 likes
  • 7 in conversation