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

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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;
  

 

View solution in original post

5 REPLIES 5
ballardw
Super User

@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
Fluorite | Level 6

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
SAS Super FREQ

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
Super User Tom
Super User

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
Super User

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
;

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 5 replies
  • 549 views
  • 0 likes
  • 4 in conversation