Looking back to get records...

[Aidaan_10]

Hi All,

               I need some help..I have some sample data below:

First Scenario:

If the result is missing I need to look back to get the result from the previous visit within each test. So for example subjid 1 for test ABC has missing result for DAY3 and WEEK1 then we get the result from DAY2 for both DAY3 and WEEK1. So basically we just have to keep looking back all the visits until we get the result. I did obtain the results by using Proc sql coalesce  when I had few visits but now I have like 80 visits and I cant program it using sql coalesce. I am looking for a macro to obtain the result.

Second Scenario:

The looking back of visits can be from another study too...as different studies are combined here. If suppose for a subject, result of one of the visit is missing from study XYZ(which is third study) then we go back to the study RWA(second study) to look for the result and if its still missing then we look into ABC(first study) to get the results. I don't have an example of sample data here.

 For example, 

DATA HAVE;
infile datalines dlm=',';
INPUT SUBJID $ TEST $ TESTCD VISIT $ VISITN RESULT ;
DATALINES;
1,ABC,1,DAY1,1,12345
1,ABC,1,DAY2,2,32457
1,ABC,1,DAY3,3,.
1,ABC,1,WEEK1,4,.
1,ABC,1,WEEK2,5,13845
1,ABC,1,WEEK3,6,.
1,ABC,1,WEEK4,7,.
1,ABC,1,EW,8,45890
1,DEF,2,DAY1,1,145
1,DEF,2,DAY2,2,.
1,DEF,2,DAY3,3,13085
1,DEF,2,WEEK1,4,.
1,DEF,2,WEEK2,5,1.45
1,DEF,2,WEEK3,6,12.34
1,DEF,2,WEEK4,7,0
1,DEF,2,EW,8,.
;
RUN;

Any help would be greatly appreciated.

Thanks...

[Aidaan_10]

Hi All,

               I need some help..I have some sample data below:

First Scenario:

If the result is missing I need to look back to get the result from the previous visit within each test. So for example subjid 1 for test ABC has missing result for DAY3 and WEEK1 then we get the result from DAY2 for both DAY3 and WEEK1. So basically we just have to keep looking back all the visits until we get the result. I did obtain the results by using Proc sql coalesce  when I had few visits but now I have like 80 visits and I cant program it using sql coalesce. I am looking for a macro to obtain the result.

Second Scenario:

The looking back of visits can be from another study too...as different studies are combined here. If suppose for a subject, result of one of the visit is missing from study XYZ(which is third study) then we go back to the study RWA(second study) to look for the result and if its still missing then we look into ABC(first study) to get the results. I don't have an example of sample data here.

 For example, 

DATA HAVE;
infile datalines dlm=',';
INPUT SUBJID $ TEST $ TESTCD VISIT $ VISITN RESULT ;
DATALINES;
1,ABC,1,DAY1,1,12345
1,ABC,1,DAY2,2,32457
1,ABC,1,DAY3,3,.
1,ABC,1,WEEK1,4,.
1,ABC,1,WEEK2,5,13845
1,ABC,1,WEEK3,6,.
1,ABC,1,WEEK4,7,.
1,ABC,1,EW,8,45890
1,DEF,2,DAY1,1,145
1,DEF,2,DAY2,2,.
1,DEF,2,DAY3,3,13085
1,DEF,2,WEEK1,4,.
1,DEF,2,WEEK2,5,1.45
1,DEF,2,WEEK3,6,12.34
1,DEF,2,WEEK4,7,0
1,DEF,2,EW,8,.
;
RUN;

Any help would be greatly appreciated.

Thanks...

[Reeza]

Please show what you expect as output.

[Aidaan_10]

My expected final output would be like for study XYZ( third study):

STUDYID  SUBJID  TEST     VISIT    RESULT

XYZ             1               ABC    DAY1      5678(Final result from study1 ABC)
XYZ             1               ABC    DAY2      32457
XYZ             1               ABC    DAY3      32457(Day2 result is being populated here)
XYZ             1               ABC    WEEK1    32457(Day2 result is being populated here)
XYZ             1               ABC   WEEK2    13845
XYZ             1               ABC   WEEK3  13845(WEEK2 result is being populated here)
XYZ             1               ABC   WEEK4  13845(WEEK2 result is being populated here)
XYZ             1               ABC   EW          45890

If suppose DAY1 is missing result in study3 we go back to study2 

study RWA( second study):

STUDYID SUBJID  TEST     VISIT    RESULT

RWA           1               ABC    DAY1     missing
RWA          1               ABC    DAY2      32457
RWA          1               ABC    DAY3      12456
RWA          1               ABC    WEEK1   3456
RWA          1               ABC   WEEK2    13845
RWA          1               ABC   WEEK3    1.12
RWA           1               ABC   WEEK4    0.00
RWA           1               ABC   EW          45890

So If the result is still missing for DAY1 from second study we go back to First study(ABC) for the result.

study ABC( First study):

STUDYID SUBJID    TEST     VISIT    RESULT

ABC           1               ABC    DAY1    5678
ABC          1               ABC    DAY2      32457
ABC          1               ABC    DAY3      12456
ABC          1               ABC    WEEK1   3456
ABC          1               ABC   WEEK2    13845
ABC          1               ABC   WEEK3    1.12
ABC           1               ABC   WEEK4    0.00
ABC           1               ABC   EW          45890

[Patrick]

@Aidaan_10 

Please make sure that your expected result matches the sample data provided. There is not study RWA in your sample data.

Also: Is there really no visit date in your data. And if not: If you're just picking sequence numbers of visits then isn't it possible that they are on different dates between studies and though you could actually pick a later date if using data from another study?

[Reeza]

Please don't post the same question multiple times.

[Aidaan_10]

Sorry about that Reeza...I closed one.

[Aidaan_10]

Any help please

[Aidaan_10]

Hi Patrick visit or study date is not considered...I just need the results for baseline calculations.

[Patrick]

@Aidaan_10 

You really need to provide sample data which allows us to create your desired data. How else do you expect us to come up with tested code?