topic Re: how to create a macro column name in proc report in SAS Programming

how to create a macro column name in proc report

tekish — Mon, 20 Nov 2017 20:45:50 GMT

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

Re: how to create a macro column name in proc report

Shmuel — Mon, 20 Nov 2017 20:24:46 GMT

Why are you using %substr() - it is intended to be used within a macro program.

try to change it into substr() (without the % symbol).

Re: how to create a macro column name in proc report

Reeza — Mon, 20 Nov 2017 20:37:24 GMT

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.

Re: how to create a macro column name in proc report

tekish — Mon, 20 Nov 2017 20:48:57 GMT

Reeza,

The reason I want to create a macro column is I have 200 column names to be labeled.

Re: how to create a macro column name in proc report

Reeza — Mon, 20 Nov 2017 20:54:09 GMT

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.