Merging datasets without a unique identifier

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

Merging datasets without a unique identifier

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


Accepted Solutions
Solution
‎06-21-2018 02:19 PM
Super User
Posts: 6,785

Re: Merging datasets without a unique identifier

A simple fix:

 

data want;

filledDay = .;

merge prescribed filled;

by patientid  genericday;

run;

View solution in original post


All Replies
PROC Star
Posts: 1,838

Re: Merging datasets without a unique identifier

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;
Contributor
Posts: 31

Re: Merging datasets without a unique identifier

Posted in reply to novinosrin

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

Solution
‎06-21-2018 02:19 PM
Super User
Posts: 6,785

Re: Merging datasets without a unique identifier

A simple fix:

 

data want;

filledDay = .;

merge prescribed filled;

by patientid  genericday;

run;

Contributor
Posts: 31

Re: Merging datasets without a unique identifier

Posted in reply to Astounding

Works great, thank you!

Super User
Posts: 10,787

Re: Merging datasets without a unique identifier

data want;
merge prescribed filled;
by patientid  genericday;
output;
call missing(of _all_);
run;
New Contributor
Posts: 4

Re: Merging datasets without a unique identifier

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

 

Super User
Posts: 10,787

Re: Merging datasets without a unique identifier

Posted in reply to itsmesuraj33

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

Start a new session to discuss your new question .

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 160 views
  • 0 likes
  • 5 in conversation