Here are some improvements/issues you can apply/work on:
Utilise "select into" to simplify the portion of your code that gets the distinct values and count and then assigns them to macro variables
Regarding "count(make)", this counts the variable make as a whole, regardless if it is null or is a repeating value. I believe you are after "count (distinct make)"
Incorrect placing of the closing ")" for the compress function. This ->
call symputx ('make',compress(make,' .'),'g')
Should be ->
call symputx ('make',compress(make,' .','g'))
You are assigning to macro variable "var",
%let var= %scan(&make,&i,"@");
but you are not using it anywhere in your code, looks like you are confusing it with &make
Would also recommend to use descriptive names for macro programs
Remember to always end your proc sql; with quit;
Try this:
proc sql;
select distinct make,
count (distinct make)
into
:make_list separated by ' ',
:n
from sashelp.cars;
quit;
%put &=make_list;
%put &=n;
%macro export_make;
%do i=1 %to &n;
%let make=%scan(&make_list,&i);
proc export data=testing (where=(make="&make."))
outfile="C:\Users\&make..xlsx"
dbms=xlsx replace;
sheet="&make." ;
run;
%end;
%mend export_make;
%export_make;
... View more
