BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Tiny_Kane
Obsidian | Level 7

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
1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

6 REPLIES 6
Astounding
PROC Star

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
Obsidian | Level 7

Thanks! It works.

Ksharp
Super User
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
Obsidian | Level 7

Thank you!

PGStats
Opal | Level 21

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
Tiny_Kane
Obsidian | Level 7

Thank you!!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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