topic Re: How to parse string with no delimiter in SAS Programming

How to parse string with no delimiter

sas123451 — Sun, 28 Oct 2018 03:49:18 GMT

I have a string variable with a bunch of CUSIPs (9-digit bond IDs) strung together without any delimiter. Each observation in the column also has different lengths, i.e., some observations have 2 bonds (18 characters) and others have up to 10 or more (90 characters). How do I create a new variable that puts each CUSIP into a separate column?

For example, I have an observation under the CUSIP column that has:

084375EJ9084375EK6084375EL4

How do I make new variables cusip1, cusip2, cusip3 with the values 084375EJ9, 084375EK6, 084375EL4 respectively?

Thank you,

Natee

Re: How to parse string with no delimiter

novinosrin — Sun, 28 Oct 2018 03:53:17 GMT

Take @Reeza 's solution from this link unless you wanna play with APP functions like I(proud disciple of Paul Dorfman) do

https://communities.sas.com/t5/SAS-Programming/Creating-an-incremented-variable-in-a-do-loop/m-p/507865#M136337

Re: How to parse string with no delimiter

Shmuel — Sun, 28 Oct 2018 07:38:45 GMT

If you prefer to have each CUSIP as separate observation then you can try:

filename cusips '<path and name>' recfm=v;

data want;
   infile cussips;
   input cussip $9.;
run;

Re: How to parse string with no delimiter

sas123451 — Sun, 28 Oct 2018 15:41:57 GMT

Thank you for your response. I do prefer to have each CUSIP as a separate observation but I do not completely understand your code. Would you mind annotating it?

Re: How to parse string with no delimiter

VDD — Sun, 28 Oct 2018 16:15:48 GMT

this is @Reeza code modified for your needs noted by @novinosrin. FYI, record 2 fulfills your condition where a record may have more columns than expected.

data have;
infile cards truncover;
input myVar $255.;
cards;
084375EJ9084375EK6084375EL4
084375EJ9082375EK6084325EL4084395EJ9084395EK6084395EL4
074375EJ9089375EK6084335EL4
064375EJ9084375EK6084345EL4
;;;;
run;
proc print data=have;
run;

data want; 
set have;
Obs = _n_; *record number to know which go together;
len = length(myVar); *legnth of variable to loop only as needed;

do i=1 to len by 9; *loop;
    value = substr(myVar, i, 9); * extract string of size 9 each time;
    output; *output;
end;
run;

*wide format;
proc transpose data=want out=wide prefix=VAR;
by Obs;
var value;
run;
proc print data=wide;
run;

output proc prints

The SAS System

Obs myVar
1 084375EJ9084375EK6084375EL4
2 084375EJ9082375EK6084325EL4084395EJ9084395EK6084395EL4
3 074375EJ9089375EK6084335EL4
4 064375EJ9084375EK6084345EL4

Re: How to parse string with no delimiter

novinosrin — Sun, 28 Oct 2018 17:12:48 GMT

Thank you @VDD for your interest and perseverance. Well done!