BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

Dear

I need to merge two data sets by 5 variables. For some OBs there are blank values in the data for the by variables. I am not getting the output when the by variables have blank values.

Data1

id             date                     RD               awk                  datapage

1            2016-07-20                                                        111000
2                                                                                      123456


data2

id              date                    RD                awk                    datapage                     vsyn

1             2016-07-20                                                         111000                          no
2                                                                                         123456                         no


code;

proc sort data=data1;
by id date rd awk datapage;
run;

proc sort data=data2;
by id date rd awk datapage;
run;

data dat3;
merge dat1(in=a) data2(in=b);
by id date rd awk datapage;
if a;
run;

Output needed;

ID              date                        datapage                                         vsyn
1            2016-07-20            111000                                                    no
2                                           123456                                                   no

 

output getting;

 

1              2016-07-20           111000                                                  no
2                                          123456

4 REPLIES 4
Reeza
Super User

Are you certain they're an exact match across all fields? 

Ksharp
Super User

I get what you want. there are something wrong in your data.

 

data Data1;
input (id             date                     RD               awk                  datapage) (:$20.);
cards;
1            2016-07-20                    . .                                    111000
2                . . .                                                                      123456
;
run;

data data2;
input (id              date                    RD                awk                    datapage                     vsyn) (:$20.);
cards;
1             2016-07-20                      . .                                   111000                          no
2                   . . .                                                                      123456                         no
;
run;
data want;
 merge data1 data2;
 by id date rd awk datapage;
run;
knveraraju91
Barite | Level 11

Thank you. I found why. I used Length(date) to find why. When the date is missing,  the length are 1 and 10 in these datasets. I fixed the lengths and it worked.

Astounding
PROC Star

MERGE will do this ... but not for the case you illustrated.  A missing value in the second data set overwrites a valid value in the first data set.  

 

There are many ways to fix this, but the easiest is if you have a one-to-one match (or a one-to-zero match).  In that case, you can switch from MERGE to UPDATE.  

 

Another easy fix:  If the missing values are limited to just one data set, mention that data set first in the MERGE statement.

 

If you have a many-to-one match, and the missing values could be in either data set, you have to jump through some small hoops.  For example

merge dataset1 (in=a) dataset2 (in=b rename=(vsyn=vsyn2));

 

Now if there is a conflict in the values for vsyn, you have to choose which one you want.  But just to get a nonmissing value, you could code:

 

if vsyn=' ' then vsyn=vsyn2;

drop vsyn2;

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
  • 4 replies
  • 865 views
  • 3 likes
  • 4 in conversation