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