topic Transposing dataset with 100 + variables in SAS Programming

Transposing dataset with 100 + variables

Adnan_Razaq — Fri, 26 Jan 2018 09:49:15 GMT

Hi All,

I have the following sample dataset

data have;
	input account_id $ Sub_no $ Apr2000 May2000 Jun2000 Jul2000;
	cards;
	10001 1 500 500 600 600
	10001 2 100 100 50 50
	10002 1 125 135 135 160
	10002 2 5 10 15 20 
	10002 99 10 15 12 36
	;
run;

The full dataset contains variables from April 2000 to December 2017.

what I want is

Account_ID Sub_No Reporting_Date Value

10001 1 Apr2000 500

10001 2 Apr2000 100

10002 1 Apr2000 125

10002 2 Apr2000 5

10002 99 Apr2000 10

10001 1 May2000 500

10001 2 May2000 100

10002 1 May2000 135

10002 2 May2000 10

10002 99 May2000 15

The code I currently have put together is

proc transpose data=have out=want (rename= (_NAME_ = reporting_date));
   by account_id sub_no;
   VAR Apr2000 may2000 jun2000 jul2000;
run;

How can I avoid typing the name of every month in the 'var' line?

Many Thanks

Adnan

Re: Transposing dataset with 100 + variables

Kurt_Bremser — Fri, 26 Jan 2018 10:02:05 GMT

Extract the column names from dictionary.columns into a macro variable, and use that in the var statement:

proc sql noprint;
select name into :transnames separated by ' '
from dictionary.columns
where libname = 'WORK' and memname = 'HAVE' and
 upcase(substr(name,1,3)) in ('JAN','FEB',....)
;
quit;

Re: Transposing dataset with 100 + variables

RW9 — Fri, 26 Jan 2018 10:11:42 GMT

An alternative to @Kurt_Bremser's suggestion of using a macro variable would be:

proc transpose data=have out=want (rename= (_NAME_ = reporting_date));
   by account_id sub_no;
   var jan: feb: mar: apr: may: jun: jul: aug: sep: oct: nov: dec:;
run;

what this does is use the : truncation, so any variable beginning with jan will be transposed, any beginning with feb etc. You could alo use a list, if you know first and last column:

proc transpose data=have out=want (rename= (_NAME_ = reporting_date));
   by account_id sub_no;
   var jan2000--dec2017;
run;