SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

how to add rows to the data set?

Accepted Solution Solved
Reply
Contributor
Posts: 61
Accepted Solution

how to add rows to the data set?

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

Accepted Solutions
Solution
‎12-29-2016 01:27 PM
Super User
Posts: 5,092

Re: how to add rows to the data set?

[ Edited ]

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

View solution in original post


All Replies
Solution
‎12-29-2016 01:27 PM
Super User
Posts: 5,092

Re: how to add rows to the data set?

[ Edited ]

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

Contributor
Posts: 61

Re: how to add rows to the data set?

Thanks! It works.

Super User
Posts: 9,687

Re: how to add rows to the data set?

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;
Contributor
Posts: 61

Re: how to add rows to the data set?

Thank you!

Respected Advisor
Posts: 4,655

Re: how to add rows to the data set?

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;
PG
Contributor
Posts: 61

Re: how to add rows to the data set?

Thank you!!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 319 views
  • 1 like
  • 4 in conversation