BookmarkSubscribeRSS Feed
sri1
Obsidian | Level 7

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

5 REPLIES 5
Quentin
Super User

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.

The Boston Area SAS Users Group is hosting free webinars!
Next webinar will be in January 2025. Until then, check out our archives: https://www.basug.org/videos. And be sure to subscribe to our our email list.
sri1
Obsidian | Level 7

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

Reeza
Super User
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
Super User

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
Obsidian | Level 7

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

SAS Innovate 2025: Register Now

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!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 986 views
  • 2 likes
  • 3 in conversation