topic Re: how to get output expected when by variables have blank values in SAS Programming

how to get output expected when by variables have blank values

knveraraju91 — Sun, 09 Oct 2016 07:44:13 GMT

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

Re: how to get output expected when by variables have blank values

Reeza — Sun, 09 Oct 2016 08:38:04 GMT

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

Re: how to get output expected when by variables have blank values

Ksharp — Sun, 09 Oct 2016 08:53:59 GMT

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;

Re: how to get output expected when by variables have blank values

Astounding — Sun, 09 Oct 2016 13:02:32 GMT

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;

Re: how to get output expected when by variables have blank values

knveraraju91 — Sun, 09 Oct 2016 16:13:52 GMT

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.