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

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

jerry898969 — Thu, 31 Mar 2016 14:11:38 GMT

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.

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

data_null__ — Thu, 31 Mar 2016 14:18:26 GMT

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;

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

RW9 — Thu, 31 Mar 2016 14:23:10 GMT

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.

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

jklaverstijn — Thu, 31 Mar 2016 14:23:15 GMT

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
;

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

Astounding — Thu, 31 Mar 2016 14:33:11 GMT

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

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

jerry898969 — Thu, 31 Mar 2016 14:34:17 GMT

Thanks data_null_.

That worked perfect for me.

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

jerry898969 — Thu, 31 Mar 2016 14:36:17 GMT

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.

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

jerry898969 — Thu, 31 Mar 2016 14:37:34 GMT

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.