The problem is that CALL SYMPUTX overwrites the previous value for every iteration of the data step, so after the data step has run, &MAKE has the last value encountered in the data step.
There are several ways to solve this. One is simply to create a macro variable for each make:
data _null_;
set try;
call symputx (cats('make',_N_),compress(make,' .'),'g');
run;
You will then have a series of macro variables, which can be used in your macro.
But this can be accomplished much simpler, by using SQL SELECT INTO:
proc sql noprint;
select count(distinct make) into :N trimmed from sashelp.cars;
select distinct make into :make1-:make&N from sashelp.cars;
quit;
You can then adapt your macro to use the make variables.
But it is better to make a simpler macro, and use SQL to generate a series of macro calls:
%macro a(make);
proc export data=testing (where=(make="&make."))
outfile="C:\Users\&make..xlsx"
dbms=xlsx replace;
sheet="&make." ;
run;
%mend ;
proc sql noprint;
select distinct cats('%a(',make,')') into :doit separated by ';';
quit;
&doit;
... View more