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
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;
@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;
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..
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.
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;
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 ;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.