Expand data

[daradanye]

Hi,

I want to expand data from this:

ID	startdate	enddate	num
1	2/10/2010	2/16/2010	[0,1,0,2,4,7,0]
2	3/11/2010	3/17/2010	[0,1,3,0,1,2,0]

to this:

ID	date	num
1	2/10/2010	0
1	2/11/2010	1
1	2/12/2010	0
1	2/13/2010	2
1	2/14/2010	4
1	2/15/2010	7
1	2/16/2010	0
2	3/11/2010	0
2	3/12/2010	1
2	3/13/2010	3
2	3/14/2010	0
2	3/15/2010	1
2	3/16/2010	2
2	3/17/2010	0

I tried to look up proc expand but it seems not working in this example.  That would be great if someone can give me some suggestions. Thanks.

[yabwon]

Do-loop plus scan function:

data have;
input ID	startdate mmddyy10.	enddate mmddyy10.	num $ 20.;
format startdate enddate mmddyy10.;
cards;
1	2/10/2010	2/16/2010	[0,1,0,2,4,7,0]
2	3/11/2010	3/17/2010	[0,1,3,0,1,2,0]
;
run;
proc print;
run;

data want;
  set have;
  format date mmddyy10.;
  i = 0;
  do date = startdate to enddate;
    i = i+1;
    N=input(scan(num, i, "[,]"),best.);
    output;
  end;
  drop startdate enddate num i;
  rename N=num;
run;
proc print;
run;

Bart

[Reeza · Accepted solution]

data want;
set have;

i=1;
do date=startdate to enddate;
num_single = scan(num,  i,  "[,]");
output;
i+1;
end;

run;

A basic data step is the best method here.

---

@daradanye wrote:

Hi,

I want to expand data from this:

ID	startdate	enddate	num
1	2/10/2010	2/16/2010	[0,1,0,2,4,7,0]
2	3/11/2010	3/17/2010	[0,1,3,0,1,2,0]

to this:

ID	date	num
1	2/10/2010	0
1	2/11/2010	1
1	2/12/2010	0
1	2/13/2010	2
1	2/14/2010	4
1	2/15/2010	7
1	2/16/2010	0
2	3/11/2010	0
2	3/12/2010	1
2	3/13/2010	3
2	3/14/2010	0
2	3/15/2010	1
2	3/16/2010	2
2	3/17/2010	0

I tried to look up proc expand but it seems not working in this example.  That would be great if someone can give me some suggestions. Thanks.

---