topic Expand data in SAS Programming

Expand data

daradanye — Mon, 14 Mar 2022 16:27:59 GMT

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.

Re: Expand data

yabwon — Mon, 14 Mar 2022 16:34:02 GMT

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

Re: Expand data

Reeza — Mon, 14 Mar 2022 16:34:54 GMT

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.