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
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;
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;
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.