topic Re: How to output variable values based on condition in macro in SAS Programming

How to output variable values based on condition in macro

sri1 — Fri, 14 Apr 2023 19:41:34 GMT

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

Re: How to output variable values based on condition in macro

Quentin — Fri, 14 Apr 2023 20:19:40 GMT

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.

Re: How to output variable values based on condition in macro

Reeza — Fri, 14 Apr 2023 21:07:54 GMT

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

Re: How to output variable values based on condition in macro

sri1 — Fri, 14 Apr 2023 21:42:43 GMT

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

Re: How to output variable values based on condition in macro

sri1 — Fri, 14 Apr 2023 21:46:06 GMT

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

Re: How to output variable values based on condition in macro

Reeza — Fri, 14 Apr 2023 22:02:14 GMT

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