DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 398
Accepted Solution

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:

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. 


Accepted Solutions
Solution
‎03-31-2016 10:33 AM
Respected Advisor
Posts: 3,777

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;

View solution in original post


All Replies
Solution
‎03-31-2016 10:33 AM
Respected Advisor
Posts: 3,777

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;
Super Contributor
Posts: 398

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

Thanks data_null_.

 

That worked perfect for me. 

Super User
Super User
Posts: 7,401

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.

Super Contributor
Posts: 398

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.

 

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

 

Thank you to all who replied.

Super Contributor
Posts: 408

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

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
;
Super User
Posts: 5,082

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

Super Contributor
Posts: 398

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

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