Multiple merge

[Danglytics]

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.

[Reeza]

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;

[Danglytics]

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

[sbb]

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.

[Reeza]

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]

[Danglytics]

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.

[Reeza]

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

[Danglytics]

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

[Danglytics]

Thanks all for your help, problem solved!

[Howles]

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.

[Ksharp]

Just another way, Can not suppress impulse to code it .:)
[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