Trying to create Macro variables

[Santt0sh]

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.

[ballardw]

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 · Accepted solution]

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