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

[jerry898969]

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:

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. 

[data_null__ · Accepted solution]

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;

[jerry898969]

Thanks data_null_.

That worked perfect for me. 

[RW9]

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.

[jerry898969]

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.

This is the steps my lead has asked me to take. 

Thank you to all who replied.

[jklaverstijn]

How about this?

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
;

[Astounding]

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 ...;

[jerry898969]

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.