topic Re: Merging datasets without a unique identifier in SAS Programming

Merging datasets without a unique identifier

Lefty — Thu, 21 Jun 2018 17:51:51 GMT

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

Re: Merging datasets without a unique identifier

novinosrin — Thu, 21 Jun 2018 18:05:16 GMT

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;

Re: Merging datasets without a unique identifier

Astounding — Thu, 21 Jun 2018 18:07:33 GMT

A simple fix:

data want;

filledDay = .;

merge prescribed filled;

by patientid genericday;

run;

Re: Merging datasets without a unique identifier

Lefty — Thu, 21 Jun 2018 18:18:51 GMT

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

Re: Merging datasets without a unique identifier

Lefty — Thu, 21 Jun 2018 18:19:24 GMT

Works great, thank you!

Re: Merging datasets without a unique identifier

Ksharp — Fri, 22 Jun 2018 13:38:45 GMT

data want;
merge prescribed filled;
by patientid  genericday;
output;
call missing(of _all_);
run;

Re: Merging datasets without a unique identifier

itsmesuraj33 — Fri, 22 Jun 2018 14:21:51 GMT

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

Re: Merging datasets without a unique identifier

Ksharp — Sat, 23 Jun 2018 10:34:41 GMT

I don't understand your question. You want SQL to do this ?

Start a new session to discuss your new question .