Hi
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
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
Take @Reeza 's solution from this link unless you wanna play with APP functions like I(proud disciple of Paul Dorfman) do
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;
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?
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
Thank you @VDD for your interest and perseverance. Well done!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: