@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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.