I have a dataset with six variables ID, TEST, VISIT, DATE, DATE1, DIFF.
I would like to create output in such a way that there should only one record per id and new variable to be created to have the additional information. Below is the sample data.
100030002 CYCLE 2 DAY 1 2014-10-15 .
100030002 CYCLE 3 DAY 1 2014-11-05 .
100030002 APTT CYCLE 4 DAY 1 2014-12-03 2014-11-26 7
100030002 INR CYCLE 4 DAY 1 2014-12-03 2014-11-26 7
100030002 APTT CYCLE 5 DAY 1 2014-12-29 2014-12-24 5
100030002 INR CYCLE 5 DAY 1 2014-12-29 2014-12-24 5
The output should have two variables ID and NEW. The variable NEW should have the following value highlighted in green and YELLOW. Brief logic for new variable.
green: As the test was missing for first two records
YELLOW: Individual Test performed(aptt, inr) performed(diff) days from first date(date),seconddate(date1)
INR AND APTT not performed: CYCLE 2 DAY 1, CYCLE 3 DAY 1 ;APTT: CYCLE 4 DAY 1 Performed 7 days from First dose(Lab date 2014-12-03, First dose date 2014-11-26), INR: CYCLE 4 DAY 1 Performed 7 days from First dose(Lab date 2014-12-03, First dose date 2014-11-26), APTT: CYCLE 5 DAY 1 Performed 5 days from First dose(Lab date 2014-12-29, First dose date 2014-12-24), INR: CYCLE 5 DAY 1 Performed 5 days from First dose(Lab date 2014-12-29, First dose date 2014-12-24)
Thanks
Rakesh
This will give you one record per id, with result as a list of visits separated by commas where data is missing.
data want;
set have;
length result $200;
by id;
retain result;
if first.id then result="";
if test="" then result=catx(",",result,visit);
if last.id then output;
run;
This will give you one record per id, with result as a list of visits separated by commas where data is missing.
data want;
set have;
length result $200;
by id;
retain result;
if first.id then result="";
if test="" then result=catx(",",result,visit);
if last.id then output;
run;
I got you started, does this suit your purposes?
data have;
infile cards dsd;
length id visit $25.;
informat date date1 yymmdd10.;
format date date1 yymmdd10.;
input id $ test $ visit $ date date1 diff;
cards;
100030002,,CYCLE 2 DAY 1,2014-10-15,,
100030002,,CYCLE 3 DAY 1,2014-11-05,,
100030002,APTT,CYCLE 4 DAY 1,2014-12-03,2014-11-26,7
100030002,INR,CYCLE 4 DAY 1,2014-12-03,2014-11-26,7
100030002,APTT,CYCLE 5 DAY 1,2014-12-29,2014-12-24,5
100030002,INR,CYCLE 5 DAY 1,2014-12-29,2014-12-24,5
;
run;
data want(keep=id new);
format new $225.;
set have;
if missing(test) then new = 'INR AND APPT not performed '|| visit;
/*if not missing(test) then new = etc;*/
run;
So what is your question?
If this is an order you need cash upfront.
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.