BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sas123451
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

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
 
 
 

 

View solution in original post

5 REPLIES 5
novinosrin
Tourmaline | Level 20

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/507...

Shmuel
Garnet | Level 18

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;
sas123451
Calcite | Level 5

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?

VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

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
 
 
 

 

novinosrin
Tourmaline | Level 20

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

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 3020 views
  • 2 likes
  • 4 in conversation