Hi all ,
I have sample dataset as below
In :
GRP | Order | n | missing | min |
A | 1 | 5 | 0 | 2 |
B | 1 | 7 | 2 | 3 |
Want output as below
GRP | order | ord | Stats | Value |
A | 1 | 1 | n | 5 |
A | 1 | 2 | min | 2 |
B | 1 | 1 | n(missing) | 7(2) |
B | 1 | 2 | min | 3 |
When missing is 0, no need to present in stats columns, if missing >0 then need to present as n(missing)
I have tried in a macro with below code but not getting expected result
%macro test;
proc sql noprint;
select MISSING into :m1 - :m2
from in;
quit;
data in2;
set in;
by order;
if &m1 ne 0 then %do;
stats=" n (missing)";
value=compress(vvalue(n)||"("||vvalue(MISSING)||")");
%end;
%else %do;
stats=" n";
value=compress(vvalue(N));
%end;
if &m2 ne 0 %then %do;
stats=" n (missing)";
value=compress(vvalue(N)||"("||vvalue(MISSING)||")");
%end;
%else %do;
stats=" n";
value=compress(vvalue(N));
%end;
output;
stats=" Min"
value=compress(vvalue(MIN));
output;
run;
data in3;
set in2
by grp;
retain ord
if first.grp then ord=0;
ord+1;
run;
proc sort data=in3
by ord order1 stats;
run;
proc transpose data=in3 out=want ;
by ord order1 stats ;
var value;
id grp;
run;
%mend;
May I know why it's not working? Appreciate response, thanks
Did you make the data you have by running PROC MEANS? If so, it might be easier to make the data you WANT with PROC MEANS, instead of transposing the output from PROC MEANS.
Thanks for your response. It's easier with data step but have multiple datasets to work and formatting to fixed decimal places.
That code has so many bugs...missing semicolons is the biggest, using macro logic when you need data step logic.
Actually nothing requires macros so far, not sure why the macro logic.
Here's the first step you were trying to do cleaned up. This answer is somewhat hardcoded to the data as the KEEP statement includes BP_STATUS which is the grouping variable
proc means data=sashelp.heart nway;
class bp_status;
var ageatDeath;
output out=in n=N NMiss=Missing min=Min;
run;
data in;
set in;
order=1;
*add just to test code;
if _n_ = 1 then missing=0;
run;
%macro test;
data in2;
set in;
by order;
if missing ne 0 then
do;
stats=" n (missing)";
value=compress(vvalue(n)||"("||vvalue(MISSING)||")");
end;
else
do;
stats=" n";
value=compress(vvalue(N));
end;
output;
stats=" Min";
value=compress(vvalue(MIN));
output;
keep bp_status order stats value;
run;
%mend;
%test;
However, I'd highly recommend checking out the TABLEN macro.
@sri1 wrote:
Hi all ,
I have sample dataset as below
In :
GRP Order n missing min A 1 5 0 2 B 1 7 2 3
Want output as below
GRP order ord Stats Value A 1 1 n 5 A 1 2 min 2 B 1 1 n(missing) 7(2) B 1 2 min 3
When missing is 0, no need to present in stats columns, if missing >0 then need to present as n(missing)
I have tried in a macro with below code but not getting expected result
%macro test;
proc sql noprint;
select MISSING into :m1 - :m2
from in;quit;
data in2;
set in;
by order;
if &m1 ne 0 then %do;
stats=" n (missing)";
value=compress(vvalue(n)||"("||vvalue(MISSING)||")");
%end;
%else %do;
stats=" n";
value=compress(vvalue(N));
%end;
if &m2 ne 0 %then %do;
stats=" n (missing)";
value=compress(vvalue(N)||"("||vvalue(MISSING)||")");
%end;
%else %do;
stats=" n";
value=compress(vvalue(N));
%end;
output;
stats=" Min"
value=compress(vvalue(MIN));
output;
run;
data in3;
set in2
by grp;
retain ord
if first.grp then ord=0;
ord+1;
run;
proc sort data=in3
by ord order1 stats;
run;proc transpose data=in3 out=want ;
by ord order1 stats ;
var value;
id grp;
run;%mend;
May I know why it's not working? Appreciate response, thanks
Hi Reeza ,thanks for your response.Missing semicolon is a typo error,thanks for link for TABLEN macro
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.