Multiple columns in one column name in proc report

[harshpatel]

Hi All,

I have a table structure like

ID Description 201201  201202  201203  201204  201205  201301  201302  201303  201304  201305

Now in proc report instead of defining multiple columns from 201201 to 201305,  do we have a option to define all columns in one macro combined variable only?

[PeterClemmensen]

"in one macro combined variable only". what do you mean by that?

[harshpatel]

Hi,

Basically my code should be

proc report data=test;

column id description  here it should be one macro variable only from that multiple columns;

define id;

define description;

define multiple columns;

run;

[andreas_lds]

You should have a closer look at the usage of macro-variables. While you could add all the bad-named variable to macro-variable and use that in the column-statement, this approach won't work in the define-statement, because define accepts only one variable.

[s_lassen · Accepted solution]

If you have data like this:

options validvarname=any;
data test;
  retain ID Description '201201'n  '201202'n  '201203'n 0;
run;

You can get the list of variable names for the columns statement like this:

proc sql noprint;
  select cats("'",name,"'n") into :names separated by ' '
  from dictionary.columns where memname='TEST' and libname='WORK' and name like '2%';
quit;

This assumes that the variables are actually named as you present it, an 201201, 201204 etc. are not column labels.

You can do a similar thing with the define statements (You will have to modify the definitions, I guess):

proc sql noprint;
  select catx(' ','define',cats("'",name,"'n"),'/analysis') into :defines separated by ';'
  from dictionary.columns where memname='TEST' and libname='WORK' and name like '2%';
quit;

This should then be usable in a PROC REPORT:

proc report data=test;
  column id description &names;
  define id;
  define description;
  &defines;
run;

If you want to see the generated macro variables before running PROC REPORT, you can just %PUT the names variable:

%put &names;

but the DEFINES variable needs to be quoted in a %PUT statement, because it contains semicolons:

%put %superq(defines);

[harshpatel]

Thanks,

Superb, it works for me

[Kurt_Bremser]

Transpose to a long dataset, and use _NAME_ as an ACROSS column in PROC REPORT.

Please post your PROC REPORT code, so we can show you how to do that.

[harshpatel]

Hi,

My dataset  looks like as below

ID Description 201201  201202  201203  201204  201205  201301  201302  201303  201304  201305

Instead of defining multiple columns in define statement i want only one define statement for 201201 to 201305

Basically my code should be

proc report data=test;

column id description  here it should be one macro variable only from that multiple columns;

define id;

define description;

define multiple columns;

run;

[Kurt_Bremser]

See this example:

Data:

data have;
input id $ description $ '201201'n  '201202'n  '201203'n;
datalines;
A XXXX 1 2 3 
B YYYY 4 5 6
;

Transpose:

proc transpose data=have out=long;
by id description;
var '20'n:;
run;

You now have a structure that your data should have in the first place. See Maxim 19.

The report code is now extremely simple, and no complicated preparation of macro variables is needed:

proc report data=long;
column id description _name_,col1;
define id / group;
define description / group;
define _name_ / across;
define col1 / analysis;
run;

Result:

[harshpatel]

Thanks,

It is great, works for fine for me