DATA Step, Macro, Functions and more

One to Many Merge Retain The "Many" Observations

Reply
New Contributor
Posts: 4

One to Many Merge Retain The "Many" Observations

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!   

 

 

Super User
Posts: 10,570

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

[ Edited ]
Posted in reply to mmattison

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-dat... 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.

 

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,570

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

Posted in reply to mmattison

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 
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
New Contributor
Posts: 4

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

Posted in reply to KurtBremser
Your scenario works but does not retain values for company, line when the
other vars are set to 0.
Super User
Posts: 10,570

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

Posted in reply to mmattison

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)

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 4 replies
  • 126 views
  • 0 likes
  • 2 in conversation