split one string into multiple row

[KinjalPatel]

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

[RW9]

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.