I have report with more than 200 columns and I want to create a macro column names.
here is the sample codes:
data class;
set sashelp.class;
nodeid100="";nodeid101="";nodeid102="";nodeid103="";nodeid104="";nodeid105="";nodeid106="";nodeid107="";
nodeid108="";nodeid109="";nodeid110="";nodeid111="";nodeid112="";nodeid113="";nodeid114="";nodeid115="";
run;
ods excel file="\test.xlsx";
proc report data=class;
column name substr(nodeid136,7);
run;
ods _all_ close;
my expected output would be:
Name | 100 | 101 | 102 | 103 | 104 | 105 | 106 | 107 | 108 | 109 | 110 | 111 | 112 | 113 | 114 | 115 |
Alfred | ||||||||||||||||
Alice | ||||||||||||||||
Barbara | ||||||||||||||||
Carol | ||||||||||||||||
Henry | ||||||||||||||||
James | ||||||||||||||||
Jane | ||||||||||||||||
Janet | ||||||||||||||||
Jeffrey | ||||||||||||||||
John | ||||||||||||||||
Joyce | ||||||||||||||||
Judy | ||||||||||||||||
Louise | ||||||||||||||||
Mary | ||||||||||||||||
Philip | ||||||||||||||||
Robert | ||||||||||||||||
Ronald | ||||||||||||||||
Thomas | ||||||||||||||||
William |
Why are you using %substr() - it is intended to be used within a macro program.
try to change it into substr() (without the % symbol).
1. What do you want in the middle? Means, maxes, etc? It does affect the data
2. It's hard to follow this, but couldn't you use labels to have the variable names appear the way you want instead? Or if the data structure doesn't lend itself to that, what about a custom format?
data class;
set sashelp.class;
nodeid100="";nodeid101="";nodeid102="";nodeid103="";nodeid104="";nodeid105="";nodeid106="";nodeid107="";
nodeid108="";nodeid109="";nodeid110="";nodeid111="";nodeid112="";nodeid113="";nodeid114="";nodeid115="";
label nodeid100='100' nodeid102='102' nodeid101='101';
run;
You can generate the labels using macros and SASHELP.VCOLUMN if desired.
Reeza,
The reason I want to create a macro column is I have 200 column names to be labeled.
It seems easier to me to create a macro to create labels. Or transpose your data and use the variable as an ACROSS variable.
@tekish wrote:
Reeza,
The reason I want to create a macro column is I have 200 column names to be labeled.
Here's an example of how that could work. You can easily get the list of variable names from PROC CONTENTS or SASHELP.VCOLUMNS and then create the labels.
Ultimately, how you choose to approach the problem is your choice.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.