Hello. I seek advice on how to parse a character string with multiple "observations" into 5 separate columns. For example, I am trying to parse the following string: {'Orva Stores': [36.99, 0.0, 'A2NEM58BFPMEIL', 1], 'J.WALKER LLC': [36.99, 0.0, 'A1B3KT3F9BMSN1', 1], 'Justin Smiles': [36.99, 0.0, 'A3SP9XX1M7ZN73', 1], 'Closeout Pro': [36.99, 0.0, 'A3IRLODW57QOBV', 1], 'Flow Dealz': [44.95, 0.0, 'A2GP4414DQTJ29', 1]} In this column there are 5 "observations" that I want to parse into 5 separate columns. The next observation (not shown) has 2 "observations" that I want to parse into 5 columns, and the next has 1, and so on. I have been able to parse the first clause, but I need help on how to continue to parse the 2nd clause, then the 3rd, etc. Also, if later observations have fewer than 5 as stated above, I need the loop to stop. In this case &MaxSeller=5 and the column I am parsing is called OfferList. Here is my code so far: data want;
set work.have;
Sellers=compress(OfferList,"'.","kads"); *removes punctuation, keeps single quotes, alpha, digits, and decimals;
/* create the 5 columns */ array names[&MaxSeller] $ 64;
array prices[&MaxSeller];
array ship[&MaxSeller];
array sellerid(&MaxSeller) $ 14;
array prime(&MaxSeller);
do i = 1 to &MaxSeller;
names[i]=scan(Sellers,i,"'",);
_NameLength=length(names[i]);
_Restofstring=strip(substr(Sellers,_NameLength+3));
prices[i]=scan(_Restofstring,i," ");
ship[i]=scan(_Restofstring,i+1," ");
_ID='/.\w{14}./o';
_ID_loc=prxmatch(_ID,_Restofstring);
sellerid[i]=strip(substr(_Restofstring,_ID_Loc+1,14));
_P='/ [01] /o';
_P_loc=prxmatch(_P,_Restofstring);
prime[i]=substr(_Restofstring,_P_loc+1,1);
end;
drop _:;
run; Thank you! I use SAS Enterprise Guide 8.1.
... View more
