How to output variable values based on condition in macro

[sri1]

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

[Quentin]

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.

[sri1]

Thanks for your response. It's easier with data step but have multiple datasets to work and formatting to fixed decimal places.

[Reeza]

It's hard to comment based on just what you've posted, but I'd guess that you're not using macros at the right step here. I'd suggest first getting it working well for one data set via data step (possibly one variable) and then getting it working for the remainder in iteration.

https://github.com/statgeek/SAS-Tutorials/blob/master/Turning%20a%20program%20into%20a%20macro.md

[Reeza]

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

---

[sri1]

Hi Reeza ,thanks for your response.Missing semicolon is a typo error,thanks for link for TABLEN macro