@pdhokriya wrote:
I do not want to additional semicolon.
Your initial post does have trailing semicolons.
You really seem to be having trouble explaining what you want. From the example data step you posted a PROC PRINT will produce this report.
Obs old 1 abcd;xyzxybav;charged; 2 ab;countrynew;zcc; 3 ab;charged; 4 ab;cd;dr; 5 ab
Now if you want to convert those strings into multiple values by splitting on the semi-colons then you could use something like this.
data tall;
row+1;
set abc ;
length new $20;
do item=1 by 1 until(new=' ');
new=scan(old,item,';');
if item=1 or new ne ' ' then output;
end;
run;
Which will produce data like this:
Obs row old new item 1 1 abcd;xyzxybav;charged; abcd 1 2 1 abcd;xyzxybav;charged; xyzxybav 2 3 1 abcd;xyzxybav;charged; charged 3 4 2 ab;countrynew;zcc; ab 1 5 2 ab;countrynew;zcc; countrynew 2 6 2 ab;countrynew;zcc; zcc 3 7 3 ab;charged; ab 1 8 3 ab;charged; charged 2 9 4 ab;cd;dr; ab 1 10 4 ab;cd;dr; cd 2 11 4 ab;cd;dr; dr 3 12 5 ab ab 1
Which you could transform back into one observation per original ROW by using PROC TRANSPOSE.
proc transpose data=tall out=want(drop=_name_) prefix=col;
by row ;
id item;
var new ;
run;
Result
Obs row col1 col2 col3 1 1 abcd xyzxybav charged 2 2 ab countrynew zcc 3 3 ab charged 4 4 ab cd dr 5 5 ab
If this is NOT what you want then please describe in detail how what you want is different. And what are the rules that explain how to get what you want.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.