BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Santt0sh
Lapis Lazuli | Level 10

 

Hi All,

 

Need your help or suggestion in creating Macro variables.

I am trying to automate Variable creation where the Macro variable can be used to in the Report creation macro and i am unable to create the Macro variables  and resolve them from the 2 datasets ABC and Sales. I am sure i am missing the fundamental of SAS Macros.

The output was expecting

for example.

C_M7 = C_Mary666667

but i am getting this.

C_M7 = C_Mary

 

data abc;
input names $;
datalines;
john
ronald
Katie
Stephen
Mike
Steve
Mary
;
run;

data sales;
input car ;
datalines;
1
2
3
4
44
55
666
666667
;
run;

proc sql;
select Names into :nme separated by ' ' from abc;
quit;
%put &nme.;
%let ct = %sysfunc(countw(&nme.));
%put ct = &ct.;
%macro t;
%do i = 1 %to  %sysfunc(countw(&nme.));
%let vrnm&i. = %scan(&nme.,&i.);
%PUT vrnm&i.  = &&vrnm&i. ;
DATA _NULL_;
SET SALES;
CALL SYMPUT (CATS("C_&&VRNM&i."),STRIP(PUT(CAR,8.2)));
RUN;
%PUT C_M&i. = C_&&VRNM&i.;
%end;
%mend;
%t;

Please see the logs attached.

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Quentin
Super User

It's hard to see what you're trying to do.  If your goal is to create 7 macro variables from two datasets that have 7 records each, you could do it using a single DATA step with CALL SYMPUTX:

 

data abc;
input names $;
datalines;
john
ronald
Katie
Stephen
Mike
Steve
Mary
;
run;

data sales;
input car ;
datalines;
1
2
3
4
55
666
7777777
;
run;

data _null_ ;
  set abc ;
  set sales ;
  call symputx(cats("C_M",_N_),cats(names,car)) ;
run ;

%put _user_ ;

Returns:

107  %put _user_ ;
GLOBAL C_M1 john1
GLOBAL C_M2 ronald2
GLOBAL C_M3 Katie3
GLOBAL C_M4 Stephen4
GLOBAL C_M5 Mike55
GLOBAL C_M6 Steve666
GLOBAL C_M7 Mary7777777
BASUG is hosting free webinars Next up: Mike Sale presenting Data Warehousing with SAS April 10 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

View solution in original post

2 REPLIES 2
ballardw
Super User

Since I don't see anything actually resembling a "report macro" you need to tell us what the macro variable names are supposed to be, what the content of each is supposed to be and might help to show where they will be used.

 

Your current code is going to end up creating multiple macro variables that will all have the last value of Car from the Sales data set.

 

When you do this:

DATA _NULL_;
SET SALES;
CALL SYMPUT (CATS("C_&&VRNM&i."),STRIP(PUT(CAR,8.2)));
RUN;

The macro variables are resolved when the code is compiled. Then every record in Sales assigns the value of the Car variable to the macro variable defined. So the result is the LAST observation value.

 

Generally attempting to place this many values into multiple macro variables is a misunderstanding of what the macro facility is for and quite often misapplied. Which is why a clear description is needed. In fact before attempting what ever this macro is doing you should have code that works without any macro elements. Then use the macro facility to replace bits that actually need to change. So it may help a lot to show your code that worked without the macros. Or what the report is supposed to look like given those data sets.

 

No clue what you may think CATS is doing. That normally combines two or more strings. You are supplying one.

 

Consider using CALL SYMPUTX instead of Symput. That removes any leading or trailing blanks from the value assigned. Which means you don't need the Strip() or other such cleaning functions quite so often.

CALL SYMPUT (CATS("C_&&VRNM&i."),STRIP(PUT(CAR,8.2)));

Call symput('macro variable name', value) is the basic use.

In the above Cats("c_&&Vrnm&i.") is the name of the macro variable created. The value assigned would be Strip(put(car,8.2).

So you appear to be missing using the proper name of the macro variable desired and the first ) for Cats is in the wrong place to create the value like "C_Mary666667"

 

Quentin
Super User

It's hard to see what you're trying to do.  If your goal is to create 7 macro variables from two datasets that have 7 records each, you could do it using a single DATA step with CALL SYMPUTX:

 

data abc;
input names $;
datalines;
john
ronald
Katie
Stephen
Mike
Steve
Mary
;
run;

data sales;
input car ;
datalines;
1
2
3
4
55
666
7777777
;
run;

data _null_ ;
  set abc ;
  set sales ;
  call symputx(cats("C_M",_N_),cats(names,car)) ;
run ;

%put _user_ ;

Returns:

107  %put _user_ ;
GLOBAL C_M1 john1
GLOBAL C_M2 ronald2
GLOBAL C_M3 Katie3
GLOBAL C_M4 Stephen4
GLOBAL C_M5 Mike55
GLOBAL C_M6 Steve666
GLOBAL C_M7 Mary7777777
BASUG is hosting free webinars Next up: Mike Sale presenting Data Warehousing with SAS April 10 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 2 replies
  • 288 views
  • 2 likes
  • 3 in conversation