topic Multiple columns in one column name in proc report in SAS Programming

Multiple columns in one column name in proc report

harshpatel — Mon, 14 Dec 2020 07:33:25 GMT

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?

Re: Multiple columns in one column name in proc report

PeterClemmensen — Mon, 14 Dec 2020 07:35:46 GMT

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

Re: Multiple columns in one column name in proc report

harshpatel — Mon, 14 Dec 2020 07:41:41 GMT

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;

Re: Multiple columns in one column name in proc report

andreas_lds — Mon, 14 Dec 2020 07:52:14 GMT

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.

Re: Multiple columns in one column name in proc report

s_lassen — Mon, 14 Dec 2020 08:10:32 GMT

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

Re: Multiple columns in one column name in proc report

Kurt_Bremser — Mon, 14 Dec 2020 08:30:23 GMT

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.

Re: Multiple columns in one column name in proc report

harshpatel — Mon, 14 Dec 2020 08:48:09 GMT

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;

Re: Multiple columns in one column name in proc report

harshpatel — Mon, 14 Dec 2020 09:01:55 GMT

Thanks,

Superb, it works for me

Re: Multiple columns in one column name in proc report

Kurt_Bremser — Mon, 14 Dec 2020 09:12:30 GMT

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:

Re: Multiple columns in one column name in proc report

harshpatel — Mon, 14 Dec 2020 09:19:21 GMT

Thanks,

It is great, works for fine for me