how to find Zip Code?

Reply
Regular Learner
Posts: 1

how to find Zip Code?

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

 

 

Super User
Posts: 13,507

Re: how to find Zip Code?

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

PROC Star
Posts: 509

Re: how to find Zip Code?

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 ;
Ask a Question
Discussion stats
  • 2 replies
  • 81 views
  • 0 likes
  • 3 in conversation