Hi again Swamy,

Ooops on my datastep.

My datastep didn't match the SQL.

I have corrected that in the datastep below.

Now the SQL and datastep match.

data main; input ID ZIPCODE; cards; 100001 20164 100002 39802 100003 45678 100004 56789 100005 67890 ; run;

data mapping; input State_Code $ Start_Zip End_Zip; cards; GA 39800 39999 XX 10000 12345 YY 50000 56789 VA 20100 20199 ; run;

* via SQL ; proc sql; create table result1 as select a.*, b.State_Code from main as a left join mapping as b on a.ZIPCODE between b.Start_Zip and b.End_Zip ; quit;

* via datastep ; data result2(drop=flag Start_Zip End_Zip); set main(in=in_a); by ID; if first.ID then flag = 0; do i = 1 to s_obs; set mapping nobs=s_obs point=i; if Start_Zip le ZIPCODE le End_Zip then do; flag = 1; output; end; end; if last.ID and flag eq 0 then do; State_Code = ''; output; end; run;

Basically, the datastep version needs to replicate the LEFT JOIN, meaning that if there is no match that the original main record is output without a state code.

Hope this is helpful.

Mark Terjeson Investment Business Intelligence Investment Management & Research Russell Investments 253-439-2367

Russell Global Leaders in Multi-Manager Investing

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Terjeson, Mark Sent: Wednesday, October 28, 2009 9:19 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: Report - Compare and Get value

Hi Swamy,

data main; input ID ZIPCODE; cards; 100001 20164 100002 39802 ; run;

data mapping; input State_Code $ Start_Zip End_Zip; cards; VA 20100 20199 GA 39800 39999 ; run;

* via SQL ; proc sql; create table result1 as select a.*, b.State_Code from main as a left join mapping as b on a.ZIPCODE between b.Start_Zip and b.End_Zip ; quit;

* via datastep ; data result2(drop=Start_Zip End_Zip); merge main mapping; if Start_Zip le ZIPCODE le End_Zip; run;

Hope this is helpful.

Mark Terjeson Investment Business Intelligence Investment Management & Research Russell Investments 253-439-2367

Russell Global Leaders in Multi-Manager Investing

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of SAS Swamy Sent: Wednesday, October 28, 2009 8:58 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Report - Compare and Get value

Hi All, I am very new to SAS, so bare with me for any basic question. I am working on a report , for getting state code I need to pass Zipcode from the main data set. Main data set would consist of the following : ID ZIPCODE ( Data set – TESTA ) 100001 20164 100002 39802 I have a mapping file , which would be : State Code Start Zip End Zip VA 20100 20199 GA 39800 39999 Since this as to be Dynamic , I wanted to make sure the best way to do this and get the State Code I think the option would be to store the entire data of the mapping file to a Data Set ( say – mapping_rule ) Being that case , can someone tell me how do I compare each rows and find the value in between the Starting and End Zipcode , and get the State Code. Thx

-Swamy