BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ECO918
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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

6 REPLIES 6
data_null__
Jade | Level 19

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

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!

ballardw
Super User

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

ECO918
Obsidian | Level 7

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!

ballardw
Super User

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.

data_null__
Jade | Level 19

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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