DATA Step, Macro, Functions and more

PROC REPORT dynamic define statement using CALL SYMPUT

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

PROC REPORT dynamic define statement using CALL SYMPUT

Hi SAS community,

I am trying to use macros and CALL SYMPUT to create dynamic column labels in proc report. I have been able to use macros to automate which tables and figures are output, but cannot automate the silly column header with "n= X".

What I want: The n= XX to be automatically derived from the data

proc report2.png

*Source Data, with n=(values) This data is then transposed for proc report;

data
source;

input
group $ frequency $ percent $;

cards;

A 5 50

B 6 75

C 9 66

D 1 25

;

run;

*Transposed data, without frequencies, for proc report;

data transposed;

input A B C D;

cards;

50 75 66 25

;

run;

 

*Proc report, with n=x manually entered;

proc report data= transposed nowd split='\';

column A B C D;

define A/ "A\n= 5"; *<-want this part to come from a macro/call symput!;

define B/ "B\n= 6";

define C/ "C\n= 9";

define D/ "D\n= 1";

run;

I have been trying to work off of the paper "Using CALL SYMPUT to Generate Dynamic Columns in Reports"  (http://support.sas.com/resources/papers/proceedings13/198-2013.pdf) , but can't figure out what tables the symput is reading from and how it's then reading the data into proc report. Since I'm pretty new to macros, I've tried the obvious and googled it at length and but am still beyond stuck.

Many, many thanks for your time and help!


Accepted Solutions
Solution
‎07-10-2015 02:25 PM
Respected Advisor
Posts: 3,799

Re: PROC REPORT dynamic define statement using CALL SYMPUT

I think it would be easier to create a "data driven" label and use IDLABEL proc transpose option to create the label. 

data source;
   input group $ frequency $ percent $;
   length idlabel $32;
   idlabel = catx(
'\',group,cats('(n=',frequency,')'));
   cards;
A 5 50
B 6 75
C 9 66
D 1 25
;
  
run;
proc print;
  
run;
proc transpose data=source out=transposed prefix=TRT_;
   var percent;
   id group;
   idlabel idlabel;
   run;
proc contents;
proc print;
  
run;
*Proc report, with n=x manually entered;
proc report data= transposed nowd split='\';
  
column TRT_:;
   define TRT_: / display;
  
run;

Capture.PNG

View solution in original post


All Replies
Solution
‎07-10-2015 02:25 PM
Respected Advisor
Posts: 3,799

Re: PROC REPORT dynamic define statement using CALL SYMPUT

I think it would be easier to create a "data driven" label and use IDLABEL proc transpose option to create the label. 

data source;
   input group $ frequency $ percent $;
   length idlabel $32;
   idlabel = catx(
'\',group,cats('(n=',frequency,')'));
   cards;
A 5 50
B 6 75
C 9 66
D 1 25
;
  
run;
proc print;
  
run;
proc transpose data=source out=transposed prefix=TRT_;
   var percent;
   id group;
   idlabel idlabel;
   run;
proc contents;
proc print;
  
run;
*Proc report, with n=x manually entered;
proc report data= transposed nowd split='\';
  
column TRT_:;
   define TRT_: / display;
  
run;

Capture.PNG
Occasional Contributor
Posts: 16

Re: PROC REPORT dynamic define statement using CALL SYMPUT

Posted in reply to data_null__

Wow, that's a much more elegant solution. I wasn't aware of the idlabel statement, so useful.

I'd still love to know how to use call symput better for general use, but your answer solves my current problem!

Thank you!

Super User
Posts: 11,343

Re: PROC REPORT dynamic define statement using CALL SYMPUT

data source;

input group $ frequency $ percent $;

str=cats("N",group);

call symputx(str,frequency);

cards;

A 5 50

B 6 75

C 9 66

D 1 25

;

run;

Creates macro variables named NA, NB, NC and ND (since your use is to indicate an N value later, group would have worked as well)

In the proc report code to use those variables:

define A/ "A\n= &NA"; *<-want this part to come from a macro/call symput!;

define B/ "B\n= &NB";

define C/ "C\n= &NC";

define D/ "D\n= &ND";

Occasional Contributor
Posts: 16

Re: PROC REPORT dynamic define statement using CALL SYMPUT

Sheesh, I feel like I've tried some variation of this 100 times and I never got it. Thanks for your reply, and showing the code. It works like a charm!

Super User
Posts: 11,343

Re: PROC REPORT dynamic define statement using CALL SYMPUT

I will not reveal how much time I spent getting such things to work the first time. Or how many times I had SAS hang because of errors in macros.

The fun begins when you have a varying number of variables and creating matching Define statements entirely. Though many of those issues are often better addressed by restructuring data and grouping carefully.

Respected Advisor
Posts: 3,799

Re: PROC REPORT dynamic define statement using CALL SYMPUT

Another nice feature of the program is that the number of columns (treatments usually) can change but the code stays the same.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 709 views
  • 4 likes
  • 3 in conversation