Help in SCAN function loop

Kalai2008 — Thu, 23 Sep 2021 20:06:33 GMT

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

Re: Help in SCAN function loop

ballardw — Thu, 23 Sep 2021 20:50:16 GMT

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

topic Re: Help in SCAN function loop in SAS Enterprise Guide

Help in SCAN function loop

Re: Help in SCAN function loop