DATA Step, Macro, Functions and more

Transposing dataset with 100 + variables

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

Transposing dataset with 100 + variables

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


Accepted Solutions
Solution
‎01-26-2018 05:36 AM
Super User
Super User
Posts: 9,427

Re: Transposing dataset with 100 + variables

Posted in reply to Adnan_Razaq

An alternative to @KurtBremser'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;

View solution in original post


All Replies
Super User
Posts: 9,914

Re: Transposing dataset with 100 + variables

Posted in reply to Adnan_Razaq

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎01-26-2018 05:36 AM
Super User
Super User
Posts: 9,427

Re: Transposing dataset with 100 + variables

Posted in reply to Adnan_Razaq

An alternative to @KurtBremser'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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 86 views
  • 0 likes
  • 3 in conversation