DATA Step, Macro, Functions and more

How to separate a space delimited field into separate lines

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

How to separate a space delimited field into separate lines

Hello, 

 

I have a field which appears in this format:    XXXX YYYY ZZZZ

 

Where it is separated by space. I would like to make a new line per space delimited string. So for the above example there would be 3 lines create. 

 

Any help would be appreciate?

 

Thanks. 


Accepted Solutions
Solution
‎06-05-2017 09:21 PM
Trusted Advisor
Posts: 1,137

Re: How to separate a space delimited field into separate lines

Please try

 

data want;
set have;
count=countw(variable,' ');
do i = 1 to count;
new=scan(variable,i,' ');
output;
end;
run;

Thanks,
Jag

View solution in original post


All Replies
PROC Star
Posts: 325

Re: How to separate a space delimited field into separate lines

can you please show how output should look like

Occasional Contributor
Posts: 7

Re: How to separate a space delimited field into separate lines

Hello, 

 

This is a basic structure which I am looking for: 

 


Before: 

idVariable
1XXXX YYYY ZZZZ
2AAAA BBBB

 

 

After: 

 

idnew_variable
1XXXX
1YYYY
1ZZZZ
2AAAA
2BBBB

 

Thanks

Solution
‎06-05-2017 09:21 PM
Trusted Advisor
Posts: 1,137

Re: How to separate a space delimited field into separate lines

Please try

 

data want;
set have;
count=countw(variable,' ');
do i = 1 to count;
new=scan(variable,i,' ');
output;
end;
run;

Thanks,
Jag
Super User
Posts: 19,769

Re: How to separate a space delimited field into separate lines

COUNTW to count the number of words

SCAN to extract the number of components.

 

n_words = countw(string);

do i=1 to n_words;
word = scan(string, i);
OUTPUT;
end;

A fully worked example is here:

https://gist.github.com/statgeek/bed5ea2c12903b38fdcf19f3f1f1aae9

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 151 views
  • 2 likes
  • 4 in conversation