topic Re: how to find Zip Code? in SAS Programming

how to find Zip Code?

laselas — Wed, 25 Apr 2018 14:39:06 GMT

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.

Ela

Re: how to find Zip Code?

ballardw — Wed, 25 Apr 2018 15:01:59 GMT

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

Re: how to find Zip Code?

kiranv_ — Wed, 25 Apr 2018 15:05:13 GMT

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 ;