How to add rows based on variable values

[kp19]

I am working on antibiotic data and need to consider the extended effect of the medication. For this I need to add data for three more days since the last date of medication.

Actual Data:

ID    Date       Antibiotic1  Antibiotic2 Antibiotic3

1  10/1/18           0                    1             0

1  10/2/18           1                    0             0

1  10/3/18           1                    0             0

Data I want:

ID    Date       Antibiotic1  Antibiotic2 Antibiotic3

1  10/1/18           0                    1             0

1  10/2/18           1                    1             0

1  10/3/18           1                    1             0

1  10/4/18           1                    1             0

1  10/5/18           1                    0             0

1  10/6/18           1                    0             0

How do I code this either in data step or using proc sql?

[novinosrin]

if " I need to add data for three more days since the last date of medication." is the requirement

the last date of medication for id 1 is 3rd oct, and why is Antibiotic2 1 till the 4th in want

EDIT: I think I am getting it. 

[kp19]

The data needs to be added for three more days for each antibiotic. Since the antibiotic2 was last given on 1/10/18 the next three days for anibiotic2 become 1.

[novinosrin]

 
data have;
input ID    Date   :mmddyy8.     Antibiotic1  Antibiotic2 Antibiotic3;
format date mmddyy8.;
cards;
1  10/1/18           0                    1             0
1  10/2/18           1                    0             0
1  10/3/18           1                    0             0
;
proc transpose data=have out=_have;
by id date ;
var Antibiotic:;
run;
proc sort data=_have out=__have;
by id _name_;
run;

data w;
set __have;
by id _name_ col1 notsorted;
output;
if last.col1 and col1 then 
do date=date+1 to date+3;
output;
end;
run;
proc sort data=w out=w1;
by id date;
run;
data w2;
set w1;
by id date _name_;
if first._name_;
run;

proc transpose data=w2 out=want;
by id date ;
var col1;
id _name_;
run;

data final_want;
set want;
array t(*)  Antibiotic:;
do _n_=1 to dim(t);
if missing(t(_n_)) then t(_n_)=0;
end;
run;

[PGStats · Accepted solution]

My take on this, a single step solution:

data have;
input ID    Date :mmddyy. Antibiotic1 Antibiotic2 Antibiotic3;
format date yymmdd10.;
datalines;
1  10/1/18           0                    1             0
1  10/2/18           1                    0             0
1  10/3/18           1                    0             0
;

data want;
array a antibiotic1-antibiotic3;
array e effect1-effect3;
array d_{3};
do until (last.id);
    set have; by id;
    do i = 1 to dim(a);
        if a{i} then do;
            d_{i} = date;
            e{i} = 1;
            end;
        else if intnx("day", date, -3) > d_{i} then e{i} = 0;
        end;
    output;
    end;

do j = 1 to 3;
    date = intnx("day", date, 1);
    do i = 1 to dim(a);
        if intnx("day", date, -3) > d_{i} then e{i} = 0;
        end;
    if max(of e{*}) then output; 
    else leave;
    end;
drop d_: i j;
run;
            
proc print data=want noobs; var id date effect:; run;

[kp19]

Thanks a lot for this one step solution! It worked out great.

[xtremexkumar]

This is dataset i want one 'add State name where Texas  ' and i also give needed output below .

I just  refer sample project

i will try    just use transpose duplicate values occur how to show below output help me.

data work1;
input STATE_NAME $	Total_Number	CATEGORY_DESCRIPTION $;
cards;
Georgia	45	< 10 years
Texas	100	< 10 years
Texas	10	< 10 years
Georgia	67	> 40 years
Texas	60	> 40 years
Texas	50	> 40 years
Georgia	100	Between 10 and 20 years
Texas	20	Between 10 and 20 years
Texas	10	Between 10 and 20 years
Georgia	56	Between 20 and 40 years
Texas	40	Between 20 and 40 years
Texas	40	Between 20 and 40 years
;
run;

just want like this output help me.