Transposing dataset with 100 + variables

[Adnan_Razaq]

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

[Kurt_Bremser]

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;

[RW9 · Accepted solution]

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;