data patients;
input EVENT_Num RX_DATE RX TRMT1 TRMT@
cards;
70940 07/22/2019 Azi 1GM 00 11
70940 07/22/2019 Cef 250MG 04 00
70086 06/12/2019 Gent240mg 28 00
70086 06/12/2019 Azi 2GM 00 21
70948 07/25/2019 Azi 1GM 00 11
70948 07/25/2019Cef 250MG 04 00
run;
I like this to be one row per event_num like this:
70940 07/22/2019 Azi 1GM& Cef 250MG 04 11 70086 06/12/2019 Gent240mg&Azi 2GM 28 21
70948 07/25/2019 Azi 1GM&Cef 250MG 04 11
data patients;
input EVENT_Num RX_DATE RX TRMT1 TRMT@
cards;
70940 07/22/2019 Azi 1GM 00 11
70940 07/22/2019 Cef 250MG 04 00
70086 06/12/2019 Gent240mg 28 00
70086 06/12/2019 Azi 2GM 00 21
70948 07/25/2019 Azi 1GM 00 11
70948 07/25/2019Cef 250MG 04 00
run;
I likt this to be like this
I like this to be one row per event_num like this:
70940 07/22/2019 Azi 1GM& Cef 250MG 04 11
70086 06/12/2019 Gent240mg&Azi 2GM 28 21
70948 07/25/2019 Azi 1GM&Cef 250MG 04 11
Please help
Your question is unclear.
Are you trying to understand how to read a file with lines of text that look like the first example data?
Or do you already have a SAS dataset that looks like the result of that first data step and you want to create a new TEXT file?
Do you have a dataset that looks like the result of the first data step and you want to transpose it into a dataset that has fewer observations and more variables?
Or something else?
data patients; input EVENT_Num RX_DATE RX TRMT1 TRMT@ cards; 70940 07/22/2019 Azi 1GM 00 11 70940 07/22/2019 Cef 250MG 04 00 70086 06/12/2019 Gent240mg 28 00 70086 06/12/2019 Azi 2GM 00 21 70948 07/25/2019 Azi 1GM 00 11 70948 07/25/2019Cef 250MG 04 00 run;
Hi I have a data set like above. I would like this tall table to be wide table one row per event_num like this
Event_Num RX_Date RX TRMT1 TRMT2
70940 07/22/2019 Azi 1GM& Cef 250MG 04 11
70086 06/12/2019 Gent240mg&Azi 2GM 28 21
70948 07/25/2019 Azi 1GM&Cef 250MG 04 11
SAS is not easy for me to post a question.
It looks like you want concatenate the values of the RX string into one long variable.
It appears you want to ignore the 0 values in TRMT1 and TRMT and just store the non-missing values into new variables named TRMT1, TRMT2, .....
It would help if you first eliminated the confusion of starting with two treatment number variables. If they really are numbers and the pattern is that only one of the two will be non-zero then just add them. If the logic is more complex than that then please explain.
Here is simple step by step approach.
First fix the two variable problem.
data step1;
set patients;
treatment = sum(trmt1,trmt);
run;
THen transpose the data.
proc transpose data=step1 out=step2_rx prefix=rx;
by event_num rx_date ;
var rx ;
run;
proc transpose data=step1 out=step2_trmt prefix=trmt ;
by event_num rx_date ;
var treatment;
run;
Now put them together than collapse the mutliple RX strings into a single string. Make sure to make it long enough.
data want;
merge step2_rx step2_trtmt ;
by event_num rx_date ;
length new_rx $2000 ;
new_rx = catx('&',of rx:);
drop rx: ;
rename new_rx=rx;
run;
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.