Help using Base SAS procedures

Multiple merge

Reply
Contributor
Posts: 66

Multiple merge

Hi,

My main dataset of customers contains a variable FSALDU, 6 characters of postalcodes across canada.
for example:
id fsaldu
1 M5G2H5
2 M5V3S2
3 L8R0L9

I have a lookup/reference file with FSALDU, but its a mix of FSA (3chars) and FSALDU (6char) and weights.
for example:
FSALDU weight
M5G 1
M5V3E9 1
L5R 0.89
L5S 0.67
T5R1R3 0.92
T5R1S2 0.54
etc..

I want to first merge my main dataset by the full fsaldu, then for anyone who did NOT match the full fsaldu, I want to merge on their FSA.
I have no problem merging on the FSALDU, but not quite sure how I can do the second step.
Thanks in advance for your help.
Super User
Posts: 17,819

Re: Multiple merge

Easiest method is to create a new variable, FSA and merge on that.



Another way is to use proc SQL and an update statement (untested).

proc sql;
update have h
set weight=(select weight from fsaldu as f
where substr(h.fsaldu, 1,3)=f.fsaldu and h.weight=.);
quit;
Contributor
Posts: 66

Re: Multiple merge

im still stuck with this..

if my weight table looks like this:
fsaldu weight
K0A 1
K0A3K0 0.69
K0A3NO 0.74

and my customer table:
id fsaldu
1 K0A3K0
2 K0A8PN
3 K0A3N0
4 K0A8T6

how can i get to the final table looking like
id fsaldu weight
1 K0A3K0 0.69
2 K0A8PN 1
3 K0A3N0 0.74
4 K0A8T6 1

Thanks Message was edited by: Danglytics
Super Contributor
Super Contributor
Posts: 3,174

Re: Multiple merge

Use DATA step with MATCH merge on you FSALDU variable. Then for any no-match conditions, you will then need to create your FSA variable as you have described in with SUBSTR in an assignment statement for positions 1-3, and do another DATA step match-merge with a MERGE / BY statement on FSA.

It's quite possible, as well, with PROC SQL.

Scott Barry
SBBWorks, Inc.
Super User
Posts: 17,819

Re: Multiple merge

There's a typo in your postal code list...

but this is what you can do using proc sql
[pre]
data have1;
input id postal $;
cards;
1 K0A3K0
2 K0A8PN
3 K0A3N0
4 K0A8T6
;
run;

data have2;
input postal $ weight;
cards;
K0A 1
K0A3K0 0.69
K0A3N0 0.74
;
run;


proc sql;
create table want as
select h1.id, h1.postal, h2.weight as weight1, h2_s.weight as weight2, case when h2.weight=. then h2_s.weight else h2.weight end as weight
from have1 as h1
left join have2 as h2
on h1.postal=h2.postal
left join have2 as h2_s
on substr(compress(h1.postal), 1,3)=compress(h2_s.postal)
order by id;
quit;
[/pre]
Contributor
Posts: 66

Re: Multiple merge

thats fantastic, thank you so much.

i'm not too familiar with creating tables in sql, how would I change the code so that for anyone mnissing a 'weight' (did not match to the lookup table) I would set their weight value to a 1?

Thanks.
Super User
Posts: 17,819

Re: Multiple merge

The first left join, joins the tables together based on the full id and weight from this is stored in variable weight1.
The second left join joins the tables together based on the FSA and the weight from this is stored in the variable weight2

The case statement is almost the equivalent of a if then else or a select statement.

See here:
http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002473682.htm


so right now it checks for a missing value in weight1, if so then assigns the weight2 value.
You can add in a line to the case statement that checks for missing both and then set it to 1.

If you can't figure it out, post your code and I'll help to debug it.

HTH
Contributor
Posts: 66

Re: Multiple merge

proc sql;
create table want as
select h1.id
, h1.postal
, h2.weight as weight1
, h2_s.weight as weight2,

case when h2.weight=. then h2_s.weight
else h2.weight
else h2.weight=. and h2_s.weight =. then weight=1
end as weight

from have1 as h1 left join have2 as h2
on h1.postal=h2.postal left join have2 as h2_s
on substr(compress(h1.postal), 1,3)=compress(h2_s.postal)
order by id;

quit;

this isnt quite working for me
Contributor
Posts: 66

Re: Multiple merge

Thanks all for your help, problem solved!
Regular Contributor
Posts: 184

Re: Multiple merge

The COALESCE function makes things somewhat more concise.

[pre]
proc sql;
create table want as
select have1.*, coalesce (h2.weight, h2_s.weight, 1) as weight
from have1 as h1
left join have2 as h2
on h1.postal=h2.postal
left join have2 as h2_s
on substr(h1.postal,1,3)=h2_s.postal
order by id;
[/pre]

Notice that I got rid of the alias for HAVE1 as well as the COMPRESS calls. I saw no need for them.

Also, better test data:

[pre]
data have1;
input id postal $;
cards;
1 K0A3K0
2 K0A8PN
3 K0A3N0
4 K0A8T6
5 XXXXXX
;

data have2;
input postal $ weight;
cards;
K0A 0.987
K0A3K0 0.69
K0A3N0 0.74
;
[/pre]


> thats fantastic, thank you so much.
>
> i'm not too familiar with creating tables in sql, how
> would I change the code so that for anyone mnissing a
> 'weight' (did not match to the lookup table) I would
> set their weight value to a 1?
>
> Thanks.
Super User
Posts: 9,681

Re: Multiple merge

Just another way, Can not suppress impulse to code it .Smiley Happy
[pre]


data have1;
input id postal $;
cards;
1 K0A3K0
2 K0A8PN
3 K0A3N0
4 K0A8T6
;
run;

data have2;
input postal $ weight;
cards;
K0A 1
K0A3K0 0.69
K0A3N0 0.74
;
run;

data want(drop=_postal);
set have1;
do i=1 to _nobs;
set have2(rename=(postal=_postal)) point=i nobs=_nobs;
if postal=_postal then do;output; leave; end;
if i=_nobs then do;
do j=1 to __nobs;
set have2(rename=(postal=_postal)) point=j nobs=__nobs;
if postal eq: strip(_postal) then do;output; leave;end;
end;
end;

end;
run;
[/pre]


Ksharp
Ask a Question
Discussion stats
  • 10 replies
  • 175 views
  • 0 likes
  • 5 in conversation