BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Adnan_Razaq
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

View solution in original post

2 REPLIES 2
Kurt_Bremser
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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