topic Re: One to Many Merge Retain The "Many" Observations in SAS Programming

One to Many Merge Retain The "Many" Observations

mmattison — Mon, 23 Oct 2017 12:27:16 GMT

I am merging two data sets. My report data has company, line, zip and 8 numerical variables (107 obs). The zip in report data is only a partial list for a given state. The template data set has all the zips for that state (1071 obs). The desired result after merging the two data sets "by zip" is the report data with company, line and 8 numerical variables and all zips in the template data set with company and line appearing on each observation with zeros in numeric variables (1071 obs).

When I merge, I am getting blank company, line for data not in template.

Help please!

Re: One to Many Merge Retain The "Many" Observations

Kurt_Bremser — Mon, 23 Oct 2017 12:33:01 GMT

Please post example data. Use the macro from https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 to create datasteps from your datasets, and post those in a code window (6th {i} or 7th "little running man" icons above the posting window, see https://communities.sas.com/t5/help/faqpage/faq-category-id/posting).

Also post your code.

Re: One to Many Merge Retain The "Many" Observations

Kurt_Bremser — Mon, 23 Oct 2017 12:38:32 GMT

Here's a quick code example:

data have;
input company $ zip $ var1 var2;
cards;
A 123456 1 1
B 123457 2 2
;
run;

data zips;
input zip $;
cards;
123456
123457
123458
;
run;

data want;
merge
  have (in=_have)
  zips (in=_zip)
;
by zip;
if not _have
then do;
  var1 = 0;
  var2 = 0;
end;
run;

proc print data=want noobs;
run;

Result:

company     zip      var1    var2

   A       123456      1       1 
   B       123457      2       2 
           123458      0       0

Re: One to Many Merge Retain The "Many" Observations

mmattison — Mon, 23 Oct 2017 13:28:57 GMT

Your scenario works but does not retain values for company, line when the
other vars are set to 0.

Re: One to Many Merge Retain The "Many" Observations

Kurt_Bremser — Mon, 23 Oct 2017 13:48:26 GMT

After reading your initial post again, I think you want a cartesian product with all the zips?

In this case it is an issue for proc sql:

proc sql;
create table want as
select
  a.company,
  b.zip as zip,
  case when b.zip = a.zip
    then var1
    else 0
  end as var1,
  case when b.zip = a.zip
    then var2
    else 0
  end as var2
from have a, zips b
order by company, zip
;
quit;

(using my example data from the other post)