BookmarkSubscribeRSS Feed
Danglytics
Calcite | Level 5
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.
10 REPLIES 10
Reeza
Super User
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
Calcite | Level 5
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Super User
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
Calcite | Level 5
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
Super User
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
Calcite | Level 5
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
Calcite | Level 5
Thanks all for your help, problem solved!
Howles
Quartz | Level 8
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
Super User
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 10 replies
  • 1304 views
  • 0 likes
  • 5 in conversation