topic Re: How to Stack data from Multiple columns to multiple rows by ID in SAS Programming

How to Stack data from Multiple columns to multiple rows by ID

shasank — Thu, 03 Aug 2023 16:25:55 GMT

Hi Community,

I need your help with below requirement. Working on SAS Base 9.4. I don't have a SAS Code yet. Not sure where to start.

I have the raw dataset in the below format.

Subject	Cond_1	Administration_1_Dt	Cond_2	Administration_2_Dt	Cond_3	Administration_3_Dt	Cond_4	Administration_4_Dt
1	Yes	22-Jan-22	No	.	No	.	Yes	4-Oct-21
2	Yes	2-Jun-22	No	.	No	.	Yes	21-Jul-22

For all the ones that have Condition as "Yes" I need to create a table that looks like below.

Output:

Subject	Condition	Administration_Dt
1	Cond_1	22-Jan-22
1	Cond_4	4-Oct-21
2	Cond_1	2-Jun-22
2	Cond_4	21-Jul-22

Any help is appreciated. Thank your or much for your time.

Re: How to Stack data from Multiple columns to multiple rows by ID

PaigeMiller — Thu, 03 Aug 2023 16:42:56 GMT

UNTESTED CODE

If you want tested code, we cannot work from screen captures or Excel data copied into your message. We need data from working SAS data step code (examples and instructions).

/* UNTESTED CODE */
/* UNTESTED CODE */
data want;
    length condition $ 8;
    set have;
    array cond cond_1-cond_4;
    array admin administration_1_dt administration_2_dt administration_3_dt administration_4_dt;
    do i=1 to dim(cond);
        if cond(i)='Yes' then do;
            condition=vname(cond(i));
            administration_dt=admin(i);
            output;
        end;
    end;
    keep subject condition administration_dt;
run;

Re: How to Stack data from Multiple columns to multiple rows by ID

mkeintz — Fri, 04 Aug 2023 19:40:33 GMT

As @PaigeMiller said. No sample data in a working data step means the code below is untested:

data want;
  set have (keep=subject cond_1 administration_1_dt rename=(cond_1=condition administration_1_dt=administration_dt))
      have (keep=subject cond_2 administration_2_dt rename=(cond_2=condition administration_2_dt=administration_dt))
      have (keep=subject cond_3 administration_3_dt rename=(cond_3=condition administration_3_dt=administration_dt))
      have (keep=subject cond_4 administration_4_dt rename=(cond_4=condition administration_4_dt=administration_dt)) ;
  by subject;
  where condition='Yes';
run;

Edit note: Corrected above to remove extraneous "SET"s.

Re: How to Stack data from Multiple columns to multiple rows by ID

andreas_lds — Fri, 04 Aug 2023 06:50:31 GMT

I would use the code suggested by @PaigeMiller with some minor modifications, but you could use proc transpose twice followed by a merge, too.

proc transpose 
      data= work.have 
      name= Condition
      out= work.cond_transposed(rename= (Col1 = CondValue) where= (CondValue = 'Yes'))
   ;
   by Subject;
   var Cond:;
run;

proc transpose 
      data= work.have 
      name= Admin
      out= work.admin_transposed(drop= Admin rename= (Col1 = Administration_Dt) where= (not missing(Administration_Dt))) 
   ;
   by Subject;
   var Administration:;
run;

data work.want;
   merge work.cond_transposed(drop= CondValue)
      work.admin_transposed
   ;
   by Subject;
run;