topic split one string into multiple row in SAS Programming

split one string into multiple row

KinjalPatel — Wed, 19 Sep 2018 11:05:46 GMT

Dear,

We have SAS dataset like below -

ID	NOTE
1	[18/07/2018] Hello [19/07/2018] Hello world
2	[20/07/2018] Hello [29/07/2018] Hello world

We need to prepare dataset like below -

ID	DATE	NAME
1	18/07/2018	Hello
1	19/07/2018	Hello world
2	20/07/2018	Hello
2	29/07/2018	Hello world

Please provide me solution using DATA step as well PROC SQL. Thanks!

Kinjal

Re: split one string into multiple row

RW9 — Wed, 19 Sep 2018 11:37:06 GMT

Why do you have a dataset like that in the first place, not good. Anyways, datastep is the best solution, sql would be complex processing.

data want;
  set have;
  length curr $200;
  do i=1 to countc(note,"[");
    curr=scan(note,i,"[");
    date=scan(curr,1,"]");
    name=scan(curr,2,"]");
    output;
  end;
run;

Note, not tested, post test data in the form of a datastep in future.