Hi guys, i've got the next problem, I need to assigned the AE of subjects 1 and 2 given by the dataset AE,
with the VISIT records given by the dataset ASSIGNED.
I am doing PROC SQL to assigned most of the VISITS, but there are two specific VISITS that I want to include
in the AE that I cant get and I need too.
fist one (01DEC2008): I explain why...
The last VISIT in my final dataset SORTWANT (see final result obtained using PROC PRINT) is 20OCT2008,
but because the AE for this subject stop the 26NOV2008, I would like extend this AE until the next record 01DEC2008.
second one (20JAN2009):...the same history...
The last VISIT in my final dataset is 22DEC2008,
but because the AE for this subject 2 stop the 01JAN2009, I would like extend this AE until the next record 20JAN2009.
Then, the question is:
Anyway to get this two extra imputed VISITS: (01DEC2008) and (20JAN2009) in my final dataset SORTWANT?
Thnaks,
V.
data AE;
informat START date9. STOP date9.;
input SUBJID START STOP;
format START STOP date9.;
cards;
1 07APR2008 26NOV2008
2 06DEC2008 01JAN2009
run;
data ASSIGNED;
informat VISIT date9.;
input SUBJID VISIT;
format VISIT date9.;
cards;
1 02APR2008
1 07APR2008
1 12MAY2008
1 19MAY2008
1 30JUN2008
1 01JUL2008
1 07JUL2008
1 07AUG2008
1 29SEP2008
1 13OCT2008
1 20OCT2008
1 01DEC2008 *IMPUTED VISIT THAT I AM INTERESTED TO INCLUDED IN MY FINAL DATASET SORTWANT;
1 22FEB2009
2 27NOV2008
2 08DEC2008
2 22DEC2008
2 20JAN2009 *IMPUTED VISIT THAT I AM INTERESTED TO INCLUDED IN MY FINAL DATASET SORTWANT;
run;
PROC SQL;
CREATE table want AS
SELECT ae.SUBJID, ae.START, ae.STOP, ASSIGNED.visit
FROM ae
LEFT join ASSIGNED a
ON ae.SUBJID=a.SUBJID
AND a.VISIT BETWEEN ae.START AND ae.STOP;
QUIT;
PROC SORT data=want out=sortwant;
by SUBJID VISIT;
RUN;
PROC PRINT data=sortwant NOOBS;
RUN;
The Result obtained is:
SUBJID START STOP VISIT
1 07APR2008 26NOV2008 07APR2008
1 07APR2008 26NOV2008 12MAY2008
1 07APR2008 26NOV2008 19MAY2008
1 07APR2008 26NOV2008 30JUN2008
1 07APR2008 26NOV2008 01JUL2008
1 07APR2008 26NOV2008 07JUL2008
1 07APR2008 26NOV2008 07AUG2008
1 07APR2008 26NOV2008 29SEP2008
1 07APR2008 26NOV2008 13OCT2008
1 07APR2008 26NOV2008 20OCT2008 *last visit subject 1;
2 06DEC2008 01JAN2009 08DEC2008
2 06DEC2008 01JAN2009 22DEC2008 *last visit subject 2;
adding a LAG period of 30 to the ae.STOP can help, but is not like i really want, I only want to get the next visit.
Any ideas?
Thanks.
PROC SQL;
CREATE table want AS
SELECT ae.SUBJID, ae.START, ae.STOP, ASSIGNED.visit
FROM ae
LEFT join ASSIGNED a
ON ae.SUBJID=a.SUBJID
AND a.VISIT BETWEEN ae.START AND ae.STOP + 30;
QUIT;
Hi,
i think the below code will help you to get those missing dates in the final dataset.
proc sql;
create table ae_ as select a.*,b.visit from ae as a left join assigned as b on
a.subjid=b.subjid where ((start<=visit<=stop) or (visit>=stop));
quit;
Thanks,
Jag
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.