topic Re: How to add rows based on variable values in SAS Data Management

How to add rows based on variable values

kp19 — Wed, 20 Feb 2019 22:36:26 GMT

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?

Re: How to add rows based on variable values

novinosrin — Wed, 20 Feb 2019 22:48:27 GMT

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.

Re: How to add rows based on variable values

kp19 — Wed, 20 Feb 2019 22:50:22 GMT

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.

Re: How to add rows based on variable values

novinosrin — Wed, 20 Feb 2019 23:19:32 GMT

 
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;

Re: How to add rows based on variable values

PGStats — Thu, 21 Feb 2019 04:11:03 GMT

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;

Re: How to add rows based on variable values

kp19 — Thu, 21 Feb 2019 16:47:45 GMT

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

Re: How to add rows based on variable values

xtremexkumar — Wed, 15 May 2019 11:20:17 GMT

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.