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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.