Hi all,
I'm using SAS 9.4 to combine prescribed medication data to filled medication data without a unique key to connect them. The problem arises when there are more rows of data in one dataset than the other.
Here is what I have:
Data prescribed
patientid PrescribedDay GenericDay
10 -16 -16
10 20 20
10 20 20
10 23 23
10 108 108
Data filled
patientid FilledDay GenericDay
10 -16 -16
10 20 20
10 23 23
10 108 108
And below is how I would like my data to look:
Data WANT
patientid prescribedDay GenericDay FilledDay
10 -16 -16 -16
10 20 20 20
10 20 20 .
10 23 23 23
10 108 108 108
So if there are 2 rows of prescribed data but only 1 row of filled data, I want to see both rows of prescribed data but have the second filledday be missing. I've tried merging on patientid and genericday, but the second row of filled data is populated as though there were two fills that day. I'm just learning SQL but so far haven't figured it out.
Thank you so much in advance!
Laurie
A simple fix:
data want;
filledDay = .;
merge prescribed filled;
by patientid genericday;
run;
Are you looking for call missing?
Data prescribed;
input patientid PrescribedDay GenericDay;
cards;
10 -16 -16
10 20 20
10 20 20
10 23 23
10 108 108
;
Data filled;
input patientid FilledDay GenericDay;
cards;
10 -16 -16
10 20 20
10 23 23
10 108 108
;
data want;
merge prescribed filled;
by patientid GenericDay;
if not first.patientid and not first.GenericDay then call missing(FilledDay);
run;
Interesting, this solution almost works, but not in situations in which there really are two rows of filled data (which I hadn't shown in my original sample datasets). Here are slightly modified sample datasets:
Data prescribed;
input patientid PrescribedDay GenericDay;
cards;
10 -16 -16
10 20 20
10 20 20
10 23 23
10 23 23
10 108 108
;
Data filled;
input patientid FilledDay GenericDay;
cards;
10 -16 -16
10 20 20
10 23 23
10 23 23
10 108 108
;
And here is how the data comes out with your code:
patientid prescribedday genericday filledday
10 -16 -16 -16
10 29 29 20
10 20 20 .
10 23 23 23
10 23 23 .
I would like that last row to also be filledday=23 because there really were two fills that day.
Thank you!!
Laurie
A simple fix:
data want;
filledDay = .;
merge prescribed filled;
by patientid genericday;
run;
Works great, thank you!
data want;
merge prescribed filled;
by patientid genericday;
output;
call missing(of _all_);
run;
hii,
I have senn your solution, you are good.......
i have a sas dataset and few queries can you please help me out with those problems.....
thanks
I don't understand your question. You want SQL to do this ?
Start a new session to discuss your new question .
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.