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
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;
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
correct result in last record should be 45 and not 23 as 01aug is less than 31aug for sub 101
its labdt and not lbdt which is present in dataset a
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;
Thanks Reeza.
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.