topic Re: how to add rows to the data set? in SAS Data Management

how to add rows to the data set?

Tiny_Kane — Wed, 28 Dec 2016 19:36:33 GMT

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

Re: how to add rows to the data set?

Astounding — Wed, 28 Dec 2016 20:53:08 GMT

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.)

Re: how to add rows to the data set?

Ksharp — Thu, 29 Dec 2016 04:29:33 GMT

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;

Re: how to add rows to the data set?

PGStats — Thu, 29 Dec 2016 05:17:35 GMT

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;

Re: how to add rows to the data set?

Tiny_Kane — Thu, 29 Dec 2016 18:27:43 GMT

Thanks! It works.

Re: how to add rows to the data set?

Tiny_Kane — Thu, 29 Dec 2016 18:28:05 GMT

Thank you!

Re: how to add rows to the data set?

Tiny_Kane — Thu, 29 Dec 2016 18:29:21 GMT

Thank you!!