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.
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;
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;
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.
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;
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
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.
Ready to level-up your skills? Choose your own adventure.