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 ;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.