@Ramsha There's several problems caused by creating new threads, besides irritating others:
1. Only a partial view of the problem. Although it does make sense to break a bigger problem down to smaller steps but unfortunately you've left out key details each time.
2. Continuity, solutions posted in other threads are ignored and we're not sure which we should respond to anymore, especially when you don't mark the previous ones solved. Someone searching may get all of these as results and no longer be able to follow the thread or solution either.
3. Work shown is implied/stated to be yours, when its actually a solution someone else proposed which can be rude. Don't know if you've done this, but it happens.
All of the above make it difficult to help you and the probability of getting an answer decreases.
Back to your original question, use SCAN() to read the last three items but there's no guarantee of correctness beyond that. If you're only interested in those fields you'll be fine. If you need to read the full data in correctly, this won't be the full solution. I'm going to bet (and I'd put money on it) that you'll find other issues you need to code around as well. However this can get you started. For your current example you could go one step further and try and get the City as well, but that won't work if the city is two names which will happen.
See the Miami Beach line for example and I added that as well to show the issue if you try that.
Good Luck.
data have;
length address $60.;
informat address $60.;
input address $60.;
cards;
288 York Street,New Haven,CT 06511,USA
301-399 South Boulevard Drive,Bainbridge,GA 39819,USA
150-151 Tremont Street,Boston,MA 02111,USA
2395 Ingleside Avenue,Macon,GA 31204,USA
1007 Merchant Street,Ambridge,PA 15003,USA
859 Washington Avenue,Miami Beach,FL 33139,USA
974 Great Plain Avenue Needham MA 02492,USA, ,
974 Great Plain Avenue Miami Beach MA 02492,USA, ,
139 Lynnfield Street,Peabody,MA 01960,USA
180 Nassau Street Princeton,NJ 08542,USA,
563 Carlsbad Village Drive,Carlsbad,CA 92008,USA
;;;;
run;
proc print data=have;run;
data want;
set have;
Country = scan(address, -1, ", ");
ZIP = scan(address, -2, ", ");
State = scan(address, -3, ", ");
*find the last location of the state acronym,
search from end otherwise you may find incorrect vlaues;
x= index(address, trim(state));
line1=substr(address, 1, x-1);
run;
... View more