BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
shasank
Quartz | Level 8

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. 

 

SubjectCond_1Administration_1_DtCond_2Administration_2_DtCond_3Administration_3_DtCond_4Administration_4_Dt
1Yes22-Jan-22No.No.Yes4-Oct-21
2Yes2-Jun-22No.No.Yes21-Jul-22

 

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

 

Output: 

Subject ConditionAdministration_Dt
1Cond_122-Jan-22
1Cond_44-Oct-21
2Cond_12-Jun-22
2Cond_421-Jul-22

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller

View solution in original post

3 REPLIES 3
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller
mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
andreas_lds
Jade | Level 19

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;

sas-innovate-2024.png

📢

ANNOUNCEMENT

The early bird rate has been extended! Register by March 18 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

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
  • 3 replies
  • 314 views
  • 2 likes
  • 4 in conversation