If the data are sorted by id:
data have;
input id visit :$11. value;
datalines;
1 first 0
1 second 0
2 first 0
2 unscheduled 0
3 first 0
run;
data want;
merge have (where=(visit='second') in=second_present)
have (where=(visit='unscheduled') in=unscheduled_present)
have;
by id;
if second_present=1 and unscheduled_present=0;
run;
... View more
Hello @serena13lee
data temp;
set have;
by id ;
retain e;
if first.id then f=1;
else if start_date-e>1 then f+1;
e=end_date;
drop e;
run;
proc sql;
create table want(drop=f) as
select a.*,Flag
from
temp a inner join (select id,f, ifc(max(end_date)-min(start_date)+1>=15,'Y','N') as Flag from temp group by id,f) b
on a.id=b.id and a.f=b.f
order by id, start_date;
quit;
... View more
Thank you so much for your quick response. Your explanation was super clear and I have a better idea of what the code is doing. You are right that my days = end_date - start_date +1. Apologies for the confusion. Since I am struggling a bit to add bits to the code I have created a new query so I don't ask too many follow up questions and waste your time (https://communities.sas.com/t5/SAS-Programming/Flagging-Overlapping-Dates-that-contribute-to-gt-15-Days/m-p/576417#M163156) But thank you again. You've been incredibly helpful!
... View more
data in; input Patient $ adtm $ Value Unit $ Type $ avisit $ bflag $; cards; A Jan12019 1 m Height baseline Y A Jan12019 50 kg Weight baseline Y A Jan52019 2 m Height visit1 . A Jan52019 55 kg Weight visit1 . B Jan12019 1 m Height baseline Y B Jan12019 50 kg Weight . . B Jan52019 55 kg Weight baseline Y ; proc sort data=in; by patient adtm descending Type; run; data want; set in; by patient adtm descending Type; retain weight_ height_; if first.adtm then do; weight_=.; height_=.; end; if type='Weight' then weight_=Value; else if type='Height' then height_=Value; if last.adtm then do; bmi=weight_/height_**2; end; output; if bmi ne . then do; type='BMI'; value=bmi; unit='kg/m2'; output; end; drop height_ weight_ bmi; run;
... View more
Thanks! Sorry for the theoretical approach. It appears that changing the area of where I added length was sufficient in resolving my issue. I will add a 'have' dataset in the future.
... View more
Honestly, I can't understand your question completely .
data have;
input Patient $ Drug $ StartDate : yymmdd10. EndDate : yymmdd10. amountperday unit $;
format StartDate EndDate : yymmdd10.;
cards;
A E 2016-02-20 2016-02-28 1 mg
A G 2016-03-20 2016-03-27 2 mg
A F 2016-02-20 2016-02-21 7 mg
A E 2016-02-20 . 4 mg
B E 2016-01-05 2016-01-24 3 mg
B G 2016-01-07 2016-01-14 5 mg
;
run;
data temp;
set have;
if enddate-startdate>=7;
run;
data want;
length new_drug $ 100;
totalamount=0;
days=0;
do until(last.patient);
set temp;
by patient;
if first.patient then new_startdate=startdate;
new_drug=cats(new_drug,drug);
totalamount+ (enddate-startdate)*amountperday;
days+(enddate-startdate);
end;
new_enddate=enddate;
format new_startdate new_enddate yymmdd10.;
keep new_: patient unit totalamount days;
run;
... View more
Via data step
proc sort data=Dataset1;
by drug;
run;
proc sort data=Dataset2(keep=drug) nodupkey;
by drug;
run;
data want;
merge Dataset1(in=a) Dataset2(in=b);
by drug;
if a and b then delete;
run;
... View more
data have;
input Patient $ VisitDate $ Value Unit $ Type $;
cards;
A Jan12019 1 m Height
A Jan12019 50 kg Weight
A Jan52019 2 m Height
A Jan52019 55 kg Weight
;
proc sort data=have;
by patient VisitDate;
run;
data want;
set have;
by patient VisitDate;
retain height_;
if first.VisitDate then height_=.;
if type='Height' then height_=Value;
if last.VisitDate then bmi=value/height_**2;
output;
if bmi ne . then do;
type='BMI';
value=bmi;
unit='kg/m2';
output;
end;
drop height_ bmi;
run;
... View more
@mkeintz is this a typo
set have (firstobs=2 obs=2) have;
Shouldn't it be
set have (firstobs=3 obs=3) have;
?
Btw, I like the
set have (where=(column1='Data 2') obs=1) have;
Very neat & efficient. Kudos!
... View more
/* Modify the way you read your data: */
data testA;
retain cat1;
input cat2 $3. value;
if missing(value) then cat1 = cat2;
else output;
datalines;
Dog.
M 7
F 5
Cat.
M 4
F 2
;
data testB;
retain cat1;
input cat2 $3. value;
if missing(value) then cat1 = cat2;
else output;
datalines;
Dog.
F 3
Cat.
M 1
F 2
;
/* Then you can use: */
proc sql;
create table final as
select
a.*,
b.value as value2
from
testA a left join
testB b on a.cat1=b.cat1 and a.cat2=b.cat2
order by cat1 desc, cat2 desc;
quit;
... View more
The code should have been like this.. data test;
input Id $1. I1 1.;
datalines;
C3
M1
F5
;
run;
data test2;
input Id $1. I1 1.;
datalines;
F3
C6
;
run;
DATA test;
SET test;
ORDER=_N_;
RUN;
PROC SORT DATA=test;
BY ID;
run;
PROC SORT DATA=test2(RENAME=(I1=VAR2));
BY ID;
run;
DATA ALL;
MERGE test test2;
BY ID;
IF ORDER=. THEN ORDER=10;
RUN;
PROC SORT DATA=ALL OUT=ALL_(DROP=ORDER);
BY ORDER;
RUN;
Proc print data = all;
run;
... View more
