Help with Transposing Columns into rows

[pp2014]

I have the following Rx monthly data.  Rx1 being the latest month data.  In my case it Sept 2019 data.  

data have;
infile cards expandtabs truncover;
input id $2. rx1 rx2 rx3 rx4 rx5 & 2.;
cards;
1 5 6 8 10 4
2 0 2 4 7 6
4 1 1 3 9 2
7 3 2 4 6 3
6 4 2 5 6 8
;

I want the output to be as below

id   Date1        Rx

1   Sept-2019   5
1   Aug-2019    6
1   Jul-2019      8
1   Jun-2019   10
1   May-2019    4
2   Sept-2019   0
2   Aug-2019    2
2   Jul-2019      4
2   Jun-2019     7
2   May-2019    6
4   Sept-2019   1
4   Aug-2019    1
4   Jul-2019      3
4   Jun-2019     9
4    May-2019   2
7    Sept-2019   3
7    Aug-2019    2
7    Jul-2019      4
7    Jun-2019     6
7    May-2019    3
6    Sept-2019   4
6    Aug-2019    2
6    Jul-2019      5
6    Jun-2019     6
6    May-2019    8

[ballardw]

---

@pp2014 wrote:

I have the following Rx monthly data.  Rx1 being the latest month data.  In my case it Sept 2019 data.  

data have;
infile cards expandtabs truncover;
input id $2. rx1 rx2 rx3 rx4 rx5 & 2.;
cards;
1 5 6 8 10 4
2 0 2 4 7 6
4 1 1 3 9 2
7 3 2 4 6 3
6 4 2 5 6 8
;

I want the output to be as below

id   Date1        Rx

1   Sept-2019   5
1   Aug-2019    6
1   Jul-2019      8
1   Jun-2019   10
1   May-2019    4
2   Sept-2019   0
2   Aug-2019    2
2   Jul-2019      4
2   Jun-2019     7
2   May-2019    6
4   Sept-2019   1
4   Aug-2019    1
4   Jul-2019      3
4   Jun-2019     9
4    May-2019   2
7    Sept-2019   3
7    Aug-2019    2
7    Jul-2019      4
7    Jun-2019     6
7    May-2019    3
6    Sept-2019   4
6    Aug-2019    2
6    Jul-2019      5
6    Jun-2019     6
6    May-2019    8

---

So how do we get a month and year from the source data?

One way to read the data to have multiple rows with the same ID and only X as the variable.

data have;
   infile cards expandtabs truncover;
   input id $2. @ ;
    do i= 1 to 5 ;
      input x @;
      output;
   end;
   input;
   drop i;
cards;
1 5 6 8 10 4 
2 0 2 4 7 6
4 1 1 3 9 2
7 3 2 4 6 3
6 4 2 5 6 8
;

This approach would create one row with a missing X for each expected line.

If you don't want missing x values then change the OUTPUT to

if not missing(x) then output;

[pp2014]

Regarding date, I will have dataset with current_date field in it which gets updated every month when data gets updated.  So current month  is Sept 2019.  I want to see Date field also while transposing..

[maguiremq]

I'm sure there's a better way to do this, but this appears to work. I'm assuming your date field will be dynamic, so you will need to figure out how to do that, but here's a start:

data have;
infile cards expandtabs truncover;
input id $2. rx1 rx2 rx3 rx4 rx5 & 2.;
cards;
1 5 6 8 10 4
2 0 2 4 7 6
4 1 1 3 9 2
7 3 2 4 6 3
6 4 2 5 6 8
;

proc sort data=have;
by id;
run;

proc format;
value $rxf
	"rx1" = "Sept-2019"
	"rx2" = "Aug-2019"
	"rx3" = "Jul-2019"
	"rx4" = "Jun-2019"
	"rx5" = "May-2019"
;

proc transpose data=have
				out=want (rename=(col1=Rx))
			   name=date1;
by id;
var rx1 rx2 rx3 rx4 rx5;
run;

data want_format (drop=date1 rename=(date2=date1));
set want;
length date2 $9.;
date2 = put(date1, $rxf.);
run;

I ran a proc compare and the results were the same.

[Tom · Accepted solution]

Let's assume have a SAS dataset already, call it HAVE, and you know the date of the first month. Let's assume you have it it a macro variable. Then just use a DO loop to convert.

%let base_date='01SEP2019'd;
data want;
  set have;
  array _rx rx1-rx5 ;
  do index=1 to dim(_rx);
    date=intnx('month',&base_date,1-index,'b');
    rx=_rx[index];
    output;
  end;
  format date date9. ;
  drop index rx1-rx5;
run;
  

[ballardw]

And a way to do while reading the data:

%let base_date='01SEP2019'd;
data have;
   infile cards expandtabs truncover;
   input id $2. @ ;
   date=intnx('month',&base_date,0,'b');
    do i= 1 to 5 ;
      input x @;
      output;
      date=intnx('month',date,-1,'b');
   end;
   input;
   drop i;
   format date date9. ;
cards;
1 5 6 8 10 4 
2 0 2 4 7 6
4 1 1 3 9 2
7 3 2 4 6 3
6 4 2 5 6 8
;