BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Lefty
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

A simple fix:

 

data want;

filledDay = .;

merge prescribed filled;

by patientid  genericday;

run;

View solution in original post

7 REPLIES 7
novinosrin
Tourmaline | Level 20

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;
Lefty
Obsidian | Level 7

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

Astounding
PROC Star

A simple fix:

 

data want;

filledDay = .;

merge prescribed filled;

by patientid  genericday;

run;

Lefty
Obsidian | Level 7

Works great, thank you!

Ksharp
Super User
data want;
merge prescribed filled;
by patientid  genericday;
output;
call missing(of _all_);
run;
itsmesuraj33
Calcite | Level 5

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

 

Ksharp
Super User

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

Start a new session to discuss your new question .

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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