BookmarkSubscribeRSS Feed
laselas
Calcite | Level 5

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

 

 

2 REPLIES 2
ballardw
Super User

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

kiranv_
Rhodochrosite | Level 12

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 ;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 324 views
  • 0 likes
  • 3 in conversation