topic Re: Help with Transposing Columns into rows in SAS Programming

Help with Transposing Columns into rows

pp2014 — Fri, 25 Oct 2019 15:06:31 GMT

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

Re: Help with Transposing Columns into rows

ballardw — Fri, 25 Oct 2019 15:20:30 GMT

@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;

Re: Help with Transposing Columns into rows

pp2014 — Fri, 25 Oct 2019 15:25:39 GMT

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..

Re: Help with Transposing Columns into rows

maguiremq — Fri, 25 Oct 2019 15:45:04 GMT

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.

Re: Help with Transposing Columns into rows

Tom — Fri, 25 Oct 2019 15:48:16 GMT

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;

Re: Help with Transposing Columns into rows

ballardw — Fri, 25 Oct 2019 20:32:16 GMT

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
;