how to add rows to the data set?

[Tiny_Kane]

Hi there,

I have an existing data set looks like the first listing below. How to add rows to the data set to make it look like the second listing? All red texts were added rows. The second listing will be, each patient will have death month+1 rows, index month shoul be from 0 to the death month. All added costs are 0. Thanks in advance!

patient ID	death month	index month	cost
1	3	0	20
1	3	1	10
1	3	3	20
2	5	2	10
2	5	3	5
2	5	4	10

patient ID	death month	index month	cost
1	3	0	20
1	3	1	10
1	3	2	0
1	3	3	20
2	5	0	0
2	5	1	0
2	5	2	10
2	5	3	5
2	5	4	10
2	5	5	0

[Astounding · Accepted solution]

Your data set is already in sorted order, so this would be a fairly direct way:

data all_months;

retain cost 0;

set have (drop=cost);

by patient_id index_month;

if last.patient_id;

do index_month = 0 to death_month;

   output;

end;

run;

data want;

merge all_months have;

by patient_id index_month;

run;

The top DATA step creates all the months needed, and the bottom DATA step merges that together with the existing data.  It's important to mention ALL_MONTHS first in the MERGE statement, so the values that you already have for COST will overwrite any 0 values.

(Note a couple of revisions to the program.)

[Tiny_Kane]

Thanks! It works.

[Ksharp]

data have;
infile cards truncover expandtabs;
input ID	death	index cost;
cards;
1	3	0	20
1	3	1	10
1	3	3	20
2	5	2	10
2	5	3	5
2	5	4	10
;
run;
proc sql;
create table temp as
 select distinct id,death from have;
quit;
data key;
 set temp;
 do index=0 to death;
  output;
 end;
run;
proc sql;
create table want as
 select a.*,coalesce(b.cost,0) as cost
  from key as a left join have as b
   on a.id=b.id and a.death=b.death and a.index=b.index;
quit;

[Tiny_Kane]

Thank you!

[PGStats]

Do it in a single data step:

data have;
infile cards truncover expandtabs;
input ID	death	index cost;
cards;
1	3	0	20
1	3	1	10
1	3	3	20
2	5	2	10
2	5	3	5
2	5	4	10
;

data want;
index = -1;
do until(last.id);
    set have(rename=(index=nextIndex cost=nextCost));
    by id;
    do index = index+1 to nextIndex-1;
        cost = 0; output;
        end;
    index = nextIndex; cost = nextCost; output;
    end;
do index = nextIndex+1 to death;
    cost = 0; output;
    end;
drop next: ;
run;

proc print noobs data=want; run;

[Tiny_Kane]

Thank you!!