data one;
input pt $ visit $ sum date ;
datalines;
a1 scr 100 01jan2015
a1 w1 150 02jan2015
a1 w2 125 03jan2015
a1 w3 80 04jan2015
a1 w4 90 05jan2015
a1 w5 70 06jan2015
a2 scr 100 01jan2015
a2 w1 125 02jan2015
a2 w2 150 03jan2015
;
run;
Can any one please help me to get below output :
pt visit sum date minimum visit_min
a1 scr 100 01jan2015 .
a1 w1 150 02jan2015 100 SCR
a1 w2 125 03jan2015 100 SCR
a1 w3 80 04jan2015 100 SCR
a1 w4 90 05jan2015 80 w3
a1 w5 70 06jan2015 100 SCR
a2 scr 100 01jan2015 .
a2 w1 125 02jan2015 100 SCR
a2 w2 150 03jan2015 100 SCR
minimum would be the minimum recorded (SUM) value from all visits prior to the current visit
If the current visit (SUM) is lowest so far in prior visits, then use (SUM) value of SCR
1. Should the last two records in your desired output should be
a2 w1 125 100 SCR
a2 w2 150 100 SCR
?
2. Can we always count of the first instance of a patient ID to be a visit type of SCR? If not, can the data be sorted in that fashion?
3. Are all visits for a patient ID to be considered at once or is it possible patient A1 would have two visit groups (perhaps separated by a time variable we don't have) that would need to be considered separately?
1. Should the last two records in your desired output should be
a2 w1 125 100 SCR
a2 w2 150 100 SCR
?
ANS: Yes , Output looks like below :
pt visit sum date minimum visit_min
a1 scr 100 01jan2015 .
a1 w1 150 02jan2015 100 SCR
a1 w2 125 03jan2015 100 SCR
a1 w3 80 04jan2015 100 SCR
a1 w4 90 05jan2015 80 w3
a1 w5 70 06jan2015 100 SCR
a2 scr 100 01jan2015 .
a2 w1 125 02jan2015 100 SCR
a2 w2 150 03jan2015 100 SCR
2. Can we always count of the first instance of a patient ID to be a visit type of SCR? If not, can the data be sorted in that fashion?
Ans: Patient ID is sorted based on the date ,So SCR can occur at any instance .
3. Are all visits for a patient ID to be considered at once or is it possible patient A1 would have two visit groups (perhaps separated by a time variable we don't have) that would need to be considered separately?
Yes ,All visit for a patient ID to be consider at once .
@Manikanta wrote:
2. Can we always count of the first instance of a patient ID to be a visit type of SCR? If not, can the data be sorted in that fashion?
Ans: Patient ID is sorted based on the date ,So SCR can occur at any instance .
Please add that case to your example data to explain what you expect as result.
In the meantime i created something really ugly. I am sure that the code will be easier to maintain by using two dow-loops - the first to find the scr-observation for a patient and the second to fill minimum and visit_min.
data want;
set one;
by pt;
length
minimum 8 visit_min $ 3
bakMinimum scrSum 8 bakVisit $ 3
rc 8
;
retain bak: scrSum;
drop bak: scrSum rc;
if _n_ = 1 then do;
declare hash h(dataset: 'work.one(keep= pt sum visit rename=(sum=minimum) where=(visit = "scr"))');
h.defineKey('pt');
h.defineData('minimum');
h.defineDone();
end;
if first.pt then do;
bakMinimum = sum;
if visit = "scr" then do;
scrSum = sum;
bakVisit = "scr";
call missing(minimum, visit_min);
end;
else do;
rc = h.find();
visit_min = "scr";
scrSum = Minimum;
bakMinimum = min(sum, minimum);
bakVisit = ifc(sum < minimum, visit, "scr");
end;
end;
else do;
if sum >= bakMinimum then do;
minimum = bakMinimum;
visit_min = bakVisit;
end;
else do;
minimum = scrSum;
visit_min = "scr";
bakMinimum = sum;
bakVisit = visit;
end;
if visit = "scr" then do;
call missing(minimum, visit_min);
end;
end;
run;
If I understood your question.
data one;
input pt $ visit $ sum date $ ;
datalines;
a1 scr 100 01jan2015
a1 w1 150 02jan2015
a1 w2 125 03jan2015
a1 w3 80 04jan2015
a1 w4 90 05jan2015
a1 w5 70 06jan2015
a2 scr 100 01jan2015
a2 w1 125 02jan2015
a2 w2 150 03jan2015
;
run;
data want;
set one;
by pt;
length visit_min $ 80 ;
retain minimum visit_min;
if first.pt then do;visit_min=' ';minimum=.;end;
output;
if first.pt then do;minimum=sum;visit_min=visit;end;
else if sum<minimum then do;minimum=sum;visit_min=visit;end;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.