Help in SCAN function loop

[Kalai2008]

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...

[ballardw]

---

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