yes the proc print at the end shows the table i want, but how do i save it as a dataset in the work library? thanks. ... View more

This seems to do everything but not give the bottom row of my desired table (total), does it? ... View more

I'm sure it can be done without a loop, but when I tried the error says "The %DO statement is not valid in open code". I wouldn't mind hearing how to fix that. ... View more

So the following code does what I want to do. Thanks Reeza for your patience.   %macro loop(dset); proc sql; select distinct var1 into: var1_value separated by '*' from &dset.; select count(distinct var1) into: num from &dset.; quit; %do i = 1 %to &num.; proc stdize data=&dset. outstat=var2_stats pctlpts = 0, 1, 5, 10, 25, 50, 75, 90, 95, 99, 100; where var1 = %scan(&var1_value.,&i.,*); var var2; run; proc export data=var2_stats outfile="multisheet" dbms=xlsx replace; sheet="var1=%scan(&var1_value.,&i.,*)"; run; %end; %mend loop; %loop(dset=have); ... View more

I tried this by specifying an output file, but still doesn't work. proc stdize data=have outstat=stats pctlpts = 0, 1, 5, 10, 25, 50, 75, 90, 95, 99, 100; by var1; var var2; run; If I don't use proc stdize, how can I achieve what I want to do, i.e. get the percentiles and save them? Proc univariate also gives no success. ... View more

That works. What if, instead of doing a simple proc freq, I'm interested in finding a number of percentile values and outputting them? I tried the following piece of code using your structure, it doesn't seem to work. What am I doing wrong? ods excel file="multisheet.xlsx" style=statistical options( sheet_interval='bygroup'); proc stdize data=have pctlpts = 0, 1, 5, 10, 25, 50, 75, 90, 95, 99, 100; by var1; var var2; run; ods excel close; ... View more

that works. but, is there a way to do the first part (proc format) automatically instead of spelling everything out? let's say i don't even need the min/median/max any more, i just need to loop through all unique values of VAR1, and have those values appear in the excel tabs. VAR1 has only 3 unique values in this case, but what if I have a large dataset with hundreds of unique values that it's infeasible to do it all by hand? ... View more

Ok let me restart from sratch. I have a "raw" dataset like this: VAR1   VAR2 1          5 1          7 2          9 2          0 3          2 3         5   At the end, I would like to have an excel file that has 3 tabs: tab "VAR1=1 (min)" containing the proc freq output of VAR2 on the subset of the data where VAR1=1 (first 2 obs of raw); tab "VAR1=2 (medium)" containing the proc freq output of VAR2 on the subset of the data where VAR1=2 (middle 2 obs of raw); tab "VAR1=3 (max)" containing the proc freq output of VAR2 on the subset of the data where VAR1=3 (last 2 obs of raw);   How can i do this by not having to specifying the numeric values taken on by VAR1 3 times? As you suggested, using a by statement could get me what I want, but I would like to be able to separate the output into 3 tabs when outputting, and naming the 3 tabs with the associated values of VAR1. Basically my trouble is how do you grab the value of VAR1 and feed that into the excel tab name? ... View more

I apologize for the lack of clarity. So I already have the dataset "var1_key_values" as shown above. I would like to do a: proc freq data=dataset; table VAR2 / out=freq_out_mean(median,maximum,minimum); where VAR1=x/y/z/w; run; Instead of doing this 4 times by specifying mean/median/maximum/minimum and x/y/z/w, I want to do them in a loop. Does that clarify things a bit? ... View more