@Kalai2008 wrote:
Hello,
I have a data look like this below,
Data Have:
ID
Data
ID1
265:~:999:~:265:~:PP:~:XXXX #~#265:~:176:~:265:~:PP:~:XXXXX #~#322:~:003:~:333:~:PP:~:XXXXX
ID2
4163:~:110:~::~:PP:~:XXX#~#46:~:507:~::~:PP:~:XXX#~#955:~:6105:~::~:PP:~:XXX
The above data has a delimiter #~# in the first record, so i need to split all columns on delimiter ":~:'
Data Want :
ID
Data
Col1
Col2
Col3
Col4
Col5
ID1
265:~:999:~:265:~:PP:~:XXXX #~#265:~:176:~:265:~:PP:~:XXXXX #~#322:~:003:~:333:~:PP:~:XXXXX
265
999
265
PP
XXXXXXX
ID1
265:~:999:~:265:~:PP:~:XXXX #~#265:~:176:~:265:~:PP:~:XXXXX #~#322:~:003:~:333:~:PP:~:XXXXX
265
176
265
PP
XXXXX
ID1
265:~:999:~:265:~:PP:~:XXXX #~#265:~:176:~:265:~:PP:~:XXXXX #~#322:~:003:~:333:~:PP:~:XXXXX
322
003
333
PP
XXXXX
ID2
4163:~:110:~::~:PP:~:XXX#~#46:~:507:~::~:PP:~:XXX#~#955:~:6105:~::~:PP:~:XXX
4163
110
PP
XXX
ID2
4163:~:110:~::~:PP:~:XXX#~#46:~:507:~::~:PP:~:XXX#~#955:~:6105:~::~:PP:~:XXX
46
507
PP
XXX
ID2
4163:~:110:~::~:PP:~:XXX#~#46:~:507:~::~:PP:~:XXX#~#955:~:6105:~::~:PP:~:XXX
955
6105
PP
XXX
I have used the below code and created the numerous columns till 15 and later i used proc transpose to data look like this,
but my issue is there are multiple records like more than 50 for id1 , id 2 etc, what i have shown is only sample of 3 .
I am looking for some do loop programs that would split all columns and list everything in the data want format. Hence my current code wont work.
Thank you for looking into this !!
data test; set sample; col1= tranwrd(scan(data,1,"~"),":",""); col2= tranwrd(scan(data,2,"~"),":",""); col3= tranwrd(scan(data,3,"~"),":",""); col4= tranwrd(scan(data,4,"~"),":",""); col5= tranwrd(tranwrd(scan(data,5,"~"),":",""),"#","");col6, 7 ,8 ,9...
Do you have a text file this was created from?
There are some options on reading text files that may be easier to use than to split this into that many variables that would read this into multiple records of Col1 to Col5 type.
Or use TRANWRD to change the whole ":~:" to a single character like | and the "#~#" to another before attempting to use scan.
This splits that long value into 5 columns, which is what I interpret as your desire, on separate rows. If that isn't the case you need to be more explicit in the description.
data junk;
x="265:~:999:~:265:~:PP:~:XXXX #~#265:~:176:~:265:~:PP:~:XXXXX #~#322:~:003:~:333:~:PP:~:XXXXX";
x=tranwrd(x,":~:","|");
x=tranwrd(x,"#~#","@");
do i=1 to countw(x,'@');
temp=scan(x,i,'@');
col1=scan(temp,1,'|');
col2=scan(temp,2,'|');
col3=scan(temp,3,'|');
col4=scan(temp,4,'|');
col5=scan(temp,5,'|');
output;
end;
drop i temp;
run;
... View more
