topic Re: grouping and transposing in SAS Programming

grouping and transposing

Dhana18 — Wed, 12 Feb 2020 19:56:51 GMT

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

Grouping and transposibg

Dhana18 — Wed, 12 Feb 2020 19:55:09 GMT

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

Re: grouping and transposing

Tom — Wed, 12 Feb 2020 20:00:11 GMT

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?

Re: grouping and transposing

Dhana18 — Wed, 12 Feb 2020 20:09:53 GMT

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.

Re: grouping and transposing

Tom — Wed, 12 Feb 2020 20:28:17 GMT

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;