Pyrite | Level 9

How to create new row for each character in each row of my variable

Hello,

I have a situation where I'm trying to track some user entered data.  They can enter data into a field on my website.

I have compressed all letters and numbers and now want to take the special characters and create a table with 1 row per character with the id.

For example

id=1 text='(+)'

id=2 text='(=)(=)'

I need to create a table that will have the following data&colon;

id=1 specChar= '('

id=1 specChar= '+'

id=1 specChar= ')'

id=2 specChar= '('

id=2 specChar= '='

id=2 specChar= ')'

id=2 specChar= '('

id=2 specChar= '='

id=2 specChar= ')'

I was looking a using a loop with substr, but I can't figure out how the syntax would be.

1 ACCEPTED SOLUTION

Accepted Solutions

Re: How to create new row for each character in each row of my variable

CHAR similar to SUBSTR and has the advantage of defining new variable with the proper length 1.

data a;
id=1; text='(+)'; output;
id=2; text='(=)(=)'; output;
run;
data b;
set a;
do i = 1 to length(text);
char = char(text,i);
output;
end;
run;
7 REPLIES 7

Re: How to create new row for each character in each row of my variable

CHAR similar to SUBSTR and has the advantage of defining new variable with the proper length 1.

data a;
id=1; text='(+)'; output;
id=2; text='(=)(=)'; output;
run;
data b;
set a;
do i = 1 to length(text);
char = char(text,i);
output;
end;
run;
Pyrite | Level 9

Re: How to create new row for each character in each row of my variable

Thanks data_null_.

That worked perfect for me.

Diamond | Level 26

Re: How to create new row for each character in each row of my variable

Out of interest, why split them up into one row per character?  It looks like a formula of some kind, perhaps pattern matching (Perl Regular Expressions or similar) may be more effective, however depends on purpose of course.

Pyrite | Level 9

Re: How to create new row for each character in each row of my variable

We have a site where users can put in code as if it was a where condition.  I want to pull out all the special characters by ID and then I'm going to roll them up into a count to see how many special characters are used.  Then I can investigate what special characters are causing issues and exclude them from being used.

Thank you to all who replied.

Rhodochrosite | Level 12

Re: How to create new row for each character in each row of my variable

data test;
length specchar \$1 charvar \$80;
input id charvar & \$char80.;

do i=1 to length(charvar);
specchar = substr(charvar, i, 1);
output;
end;

drop charvar i;
cards;
1 een lekker lange tekst met 1 of 2 cijfers erin
;
PROC Star

Re: How to create new row for each character in each row of my variable

Here's a small issue to consider.  Is it possible that the incoming field will be blank?  If so, do you want to output a single blank (vs. output no record at all)?

The LENGTH function returns a minimum value of 1, even when the incoming field is blank.  You might want to condition on:

if text > ' ' then do i=1 to ...;

Pyrite | Level 9

Re: How to create new row for each character in each row of my variable

I'm doing that check in the data table before I use data_null_'s code.  So it will eleminate any blank rows.

Thanks for pointing that out.

Discussion stats
• 7 replies
• 1623 views
• 3 likes
• 5 in conversation