topic Re: Looking back to get records... in SAS Programming

Looking back to get records...

Aidaan_10 — Tue, 26 Feb 2019 21:53:01 GMT

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...

Baseline calculation...

Aidaan_10 — Tue, 26 Feb 2019 21:40:41 GMT

Hi All,

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

First Scenario:

Second Scenario:

For example,

Any help would be greatly appreciated.

Thanks...

Re: Baseline calculation...

Reeza — Tue, 26 Feb 2019 22:03:38 GMT

Please show what you expect as output.

Re: Looking back to get records...

Reeza — Tue, 26 Feb 2019 22:05:14 GMT

Please don't post the same question multiple times.

Re: Looking back to get records...

Aidaan_10 — Tue, 26 Feb 2019 22:11:13 GMT

Sorry about that Reeza...I closed one.

Re: Baseline calculation...

Aidaan_10 — Tue, 26 Feb 2019 22:35:03 GMT

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

Re: Looking back to get records...

Aidaan_10 — Sun, 03 Mar 2019 20:13:23 GMT

Any help please

Re: Baseline calculation...

Patrick — Sun, 03 Mar 2019 21:46:39 GMT

@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?

Re: Looking back to get records...

Aidaan_10 — Sun, 03 Mar 2019 21:59:05 GMT

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

Re: Looking back to get records...

Patrick — Mon, 04 Mar 2019 10:02:31 GMT

@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?