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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

14 REPLIES 14
PaigeMiller
Diamond | Level 26
tranname="&newname";

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

--
Paige Miller
Vi_
Fluorite | Level 6 Vi_
Fluorite | Level 6

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

 

 

 

Reeza
Super User

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:

 

 

ballardw
Super User

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

Reeza
Super User

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.;
SuryaKiran
Meteorite | Level 14

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
Tom
Super User Tom
Super User

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;

 

Kurt_Bremser
Super User

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.

Vi_
Fluorite | Level 6 Vi_
Fluorite | Level 6

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. 

 

 

 

 

 

 

 

 

 

Tom
Super User Tom
Super User

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

 

Vi_
Fluorite | Level 6 Vi_
Fluorite | Level 6

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

 

 

Tom
Super User Tom
Super User

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.

 

Vi_
Fluorite | Level 6 Vi_
Fluorite | Level 6

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 14 replies
  • 1813 views
  • 8 likes
  • 7 in conversation