BookmarkSubscribeRSS Feed
mmattison
Calcite | Level 5

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!   

 

 

4 REPLIES 4
Kurt_Bremser
Super User

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 
mmattison
Calcite | Level 5
Your scenario works but does not retain values for company, line when the
other vars are set to 0.
Kurt_Bremser
Super User

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)

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 955 views
  • 0 likes
  • 2 in conversation