topic Proc sql join in SAS Procedures

Proc sql join

Yulka — Wed, 08 Oct 2008 20:35:38 GMT

There are 5 tables:
1. Year05d: 5 variables + 2 keys: zip naics
2. Year06d: 5 variables + 2 keys: zip naics
3. Year05t: 3 variables + key: zip
4. Year06t 3 variables + key: zip
5. look-up table: 2 variables: naics sic

I need one table that is unique at zip+naics level and contains all observations and all the variables from all 5 tables.
With data step the logic was as following:
1. merge Year05d Year06d by zip naics
2. merge Year05t Year06t by zip
3. merge step1 and step2
4. merge step3 and look-up table by naics

how do i do that with sql in one step?

Re: Proc sql join

1162 — Fri, 10 Oct 2008 17:40:24 GMT

I would start with something like this. Keep in mind that there's only one way to get all the observations and also be unique at the zip+naics level and that is if your first two tables only have one observation per zip+naics level and your second two tables only have one observatikon per zip level.

[pre]
proc sql;
create table all as
select *
from year05d a, year06d b, year05t c, year06t d, look-up table e
where a.zip = b.zip
and a.naics = b.naics
and c.zip = d.zip
and c.zip = a.zip
and a.naics = e.naics;
quit;
[/pre] Message was edited by: 1162