Hi together, I need your help please!!!
I have 1 table with 3 columns.: the Zip_code_star , Zip_Code_end and the city and in other table I have the Zip_code of my customer but not the city.
I need to get the city of my customer. So I need to proof if the Zip_Code of my Customer is between de intervale from Zip_code_star and Zip_Code_end
Sample.
Table 1
Zip_Code_star | Zip_Code_End | City |
63928 | 74731 | Baden-Württemberg |
Table 2
Customer_name | ZIP_code | City |
XXXX | 69256 | ??? |
There is posible to do that with proc sql?
Example
proc sql;
create table total as c
select a.Customer_name,a.Zip_Code,b.Zip_Code_star, b.Zip_Code_End
from PLZ_Cod as a, Cus_PLZ as b
where b.PLZ_an <= a.Zip_Code <= b.PLZ_End ;
quit;
Thanks in advance for your help.
BR
Ela
what are PLZ_an and PLZ_end? You "sample" says that the variables you want to compare Zipc_ode to are Zip_code_star and Zip_code_end. Also since you called your example data Table 1 and Table 2 we do not know which data set PLZ_Cod or Cus_PLZ is which. I can guess but you should be explicit and consistent in code and references.
I think you have your a and b aliases reversed in
from PLZ_Cod as a, Cus_PLZ as b
something like below
data one;
input zip_Code_start Zip_Code_End City $40.;
datalines;
63928 74731 Baden-Württemberg
61111 61798 otherplace
;
data two;
input Customer_name $ ZIP_code ;
datalines;
XXXX 69256
yyyy 61212
;
proc sql;
select b.*, a.city
from one a
inner join
two b
on ZIP_code between zip_Code_start and Zip_Code_End ;
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.
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.