array

[eric2]

Hi,

I have a dataset and i need to compare 4 records against lab date and output the earliest rslt where cdx is less than lbdt. Any suggestions?

data a;
input subject $ labdt $ cd1 $ cd2 $ cd3 $ cd4 $ rslt;
cards;
101 15JUN15 17JUN15 18JUN15 29JUN15 31AUG15 22
101 20JUN15 17JUN15 18JUN15 29JUN15 31AUG15 23
101 01AUG15 17JUN15 18JUN15 29JUN15 31AUG15 45
102 19JUN15 17JUN15 28JUN15 29JUN15 30SEP15 44
102 28JUN15 17JUN15 28JUN15 29JUN15 30SEP15 47
;
run;

I need the result as

subject date            rslt

 101     17JUN2015  22

101     18JUN2015  22

101     29JUN2015  23

101     31AUG2015  23

records 3 and 4 are 23 as the earliest rslt less than cd3, cd4 date compared to labdate is 23.

Thx

[eric2]

or i can have the two datasets as below

data a;
input subject $ labdt $ rslt;
cards;
101 15JUN15  22
101 20JUN15  23
101 01AUG15  45
102 19JUN15  44
102 28JUN15  47
;
run;

data b;
input subject $ cd $ ;
cards;
101  17JUN15

101 18JUN15

101 29JUN15

101 31AUG15 
102  17JUN15

102 28JUN15

102 29JUN15

102 30SEP15 
;
run;

[error_prone]

Maybe it's the lack of coffeine, but where is the variable lbdt? Please fix the description so that the variables you have in your example datasets are used.

[eric2]

there is no labdt in dataset b as i need to compare dates in dataset b against a and pull out the result where dates in a is less than b. so total obs in final dataset should be equal to dataset b

[eric2]

correct result in last record should be 45 and not 23 as 01aug is less than 31aug for sub 101

[error_prone]

Your description is confusing. You saisd "I have a dataset and i need to compare 4 records against lab date and output the earliest rslt where cdx is less than lbdt." - still not clear where to find the variable lbdt.

[eric2]

its  labdt and not lbdt which is present in dataset a

[Reeza]

I have a dataset and i need to compare 4 records against lab date and output the earliest rslt where cdx is less than lbdt. Any suggestions?

data a;
input subject $ labdt $ cd1 $ cd2 $ cd3 $ cd4 $ rslt;
cards;
101 15JUN15 17JUN15 18JUN15 29JUN15 31AUG15 22
101 20JUN15 17JUN15 18JUN15 29JUN15 31AUG15 23
101 01AUG15 17JUN15 18JUN15 29JUN15 31AUG15 45
102 19JUN15 17JUN15 28JUN15 29JUN15 30SEP15 44
102 28JUN15 17JUN15 28JUN15 29JUN15 30SEP15 47
;

For the Third Record your text says the result is 23, but you show 45. 

The 17th JUN is the earliest date and it's less than the lab date of 01August so I'm confused as to how the result is not 45 in your output. Or do you want the latest result that's before lab date which corresponds to the most recent before the lab, which is the max, not the min. 

And how does the RSLT not just stay the same, how does the CD align with the results?

Assuming you actually want the largest value before the lab date the logic would be as follows:

data have;
    informat labdt cd1 cd2 cd3 cd4 date9.;
    format labdt cd: date9.;
    input subject $ labdt cd1 cd2 cd3 cd4 rslt;
    cards;
101 15JUN15 17JUN15 18JUN15 29JUN15 31AUG15 22
101 20JUN15 17JUN15 18JUN15 29JUN15 31AUG15 23
101 01AUG15 17JUN15 18JUN15 29JUN15 31AUG15 45
102 19JUN15 17JUN15 28JUN15 29JUN15 30SEP15 44
102 28JUN15 17JUN15 28JUN15 29JUN15 30SEP15 47
;

data want;
    set have;
    array cd(4) cd1-cd4;
    call sortn(of cd(*)); *sort the array in ascending order;

    do i=dim(cd) to 1 by -1; *loop through it backwards, starting from max;

        if cd(i) <=labdt then
            do;
                * check if less than lab date;
                max=cd(i);
                leave;
            end;
    end;
    format max date9.;
    keep subject labdt max rslt;
run;

[eric2]

Thanks Reeza.