topic Re: PROC REPORT dynamic define statement using CALL SYMPUT in SAS Programming

PROC REPORT dynamic define statement using CALL SYMPUT

ECO918 — Fri, 10 Jul 2015 17:51:07 GMT

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

*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!

Re: PROC REPORT dynamic define statement using CALL SYMPUT

data_null__ — Fri, 10 Jul 2015 18:25:42 GMT

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;

Re: PROC REPORT dynamic define statement using CALL SYMPUT

ECO918 — Fri, 10 Jul 2015 19:35:29 GMT

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!

Re: PROC REPORT dynamic define statement using CALL SYMPUT

ballardw — Fri, 10 Jul 2015 21:16:06 GMT

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";

Re: PROC REPORT dynamic define statement using CALL SYMPUT

data_null__ — Fri, 10 Jul 2015 21:30:43 GMT

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

Re: PROC REPORT dynamic define statement using CALL SYMPUT

ECO918 — Fri, 10 Jul 2015 21:42:20 GMT

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!

Re: PROC REPORT dynamic define statement using CALL SYMPUT

ballardw — Fri, 10 Jul 2015 22:44:10 GMT

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.