Dear,
I need to flag only one obs by id test visitwindow.
In the visitvindow, first I have to search for scheduled visit(visit=1). If scheduled visit is there within visitwindow then it should be flagged.
If there are more than one scheduled visits within a visitwindow then flag last one. (id=2)
if there are no scheduled visits(visit=un) then flag last one.
Please help. Thank you
output needed:
id test visit visitwindow date flag
1 dbp 1 1 2014-07-30 Y
1 dbp un 1 2014-08-10
2 dbp 1 1 2014-07-30
2 dbp 1 1 2014-08-10 Y
3 dbp un 1 2014-07-30
3 dbp un 1 2014-08-10 Y
4 dbp 1 1 2014-07-30 Y
;
data one; input id test$ visit $ visitwindow vsdtc $10.; datalines;
4 dbp 1 1 2014-07-30 1 dbp 1 1 2014-07-30 1 dbp un 1 2014-08-10 2 dbp 1 1 2014-07-30 2 dbp 1 1 2014-08-10 3 dbp un 1 2014-07-30 3 dbp un 1 2014-08-10
Let me take a first pass at this by using LAST. processing.
proc format;
value unkvis .='UN';
run;
data have;
input id test$ visit ??:3. visitwindow vsdtc :yymmdd10.;
format vsdtc date9. visit unkvis.;
datalines;
4 dbp 1 1 2014-07-30
1 dbp 1 1 2014-07-30
1 dbp un 1 2014-08-10
2 dbp 1 1 2014-07-30
2 dbp 1 1 2014-08-10
3 dbp un 1 2014-07-30
3 dbp un 1 2014-08-10
run;
proc sort data=have;
by id visit;
run;
data want;
set have;
by id visit;
if last.id then flag='Y';
run;
proc print data=want;
run;
Thank you for the support. I am not getting the output I need. For the ID=1, I am getting output (flag='Y') where visit='un'. If scheduled visit is present in a visitwindow then it should be flagged. Thank you for your time.
Visit='un' is an unscheduled visit.
The trick to doing the right thing is "knowing" how many scheduled visits there are. Here's one way to program this.
Since your dates are in YYMMDD form, sorting on a character date will get the observations in the proper order:
proc sort data=have;
by id test visitwindow sasdate;
run;
Then a DATA step can count the number of scheduled visits (in the top loop) and flag accordingly (in the bottom loop).
data want;
n_scheduled=0;
do until (last.visitwindow);
set have;
by id test visitwindow;
if visit='1' then n_scheduled + 1;
end;
total_n=0;
do until (last.visitwindow);
set have;
by id test visitwindow;
flag=' ';
if visit='1' then do;
total_n + 1;
if total_n = n_scheduled then flag='Y';
end;
if last.visitwindow and n_scheduled=0 then flag='Y';
output;
end;
drop n_scheduled total_n;
run;
Try this:
data one;
input id test $ visit $ visitwindow vsdt yymmdd10.;
format vsdt yymmddd10.;
datalines;
4 dbp 1 1 2014-07-30
1 dbp 1 1 2014-07-30
1 dbp un 1 2014-08-10
2 dbp 1 1 2014-07-30
2 dbp 1 1 2014-08-10
3 dbp un 1 2014-07-30
3 dbp un 1 2014-08-10
;
run;
proc sort data=one;
by id test visitwindow descending vsdt;
run;
data want (keep=id test visitwindow visit vsdt flag);
set one;
by id test visitwindow;
retain set_flg;
if first.visitwindow
then set_flg = 0;
if visit ne 'un'
then do;
if not set_flg
then flag = 'Y';
set_flg = 1;
end;
run;
proc sort data=want;
by id test visitwindow vsdt;
run;
data want;
set want;
by id test visitwindow;
retain un_flg;
if first.visitwindow then un_flg = 1;
if visit ne 'un' then un_flg = 0;
if last.visitwindow and un_flg then flag = 'Y';
drop un_flg;
run;
proc print data=want noobs;
run;
The result:
id test visit visitwindow vsdt flag 1 dbp 1 1 2014-07-30 Y 1 dbp un 1 2014-08-10 2 dbp 1 1 2014-07-30 2 dbp 1 1 2014-08-10 Y 3 dbp un 1 2014-07-30 3 dbp un 1 2014-08-10 Y 4 dbp 1 1 2014-07-30 Y
matches your needed output.
data have;
input id test$ visit $ visitwindow vsdtc :yymmdd10.;
format vsdtc date9. ;
datalines;
1 dbp 1 1 2014-07-30
1 dbp un 1 2014-08-10
2 dbp 1 1 2014-07-30
2 dbp 1 1 2014-08-10
3 dbp un 1 2014-07-30
3 dbp un 1 2014-08-10
4 dbp 1 1 2014-07-30
run;
data want;
do i=1 by 1 until(last.id);
set have;
by id;
if visit=1 then idx=i;
end;
do j=1 by 1 until(last.id);
set have;
by id;
flag=' ';
if idx=j then flag='Y';
if last.id and missing(idx) then flag='Y';
output;
end;
drop i j idx;
run;
proc print;run;
Dear,
Your code worked. But for some OBS, the code needs to be modified. I tried but did not get the output I need.
There are two more variable added to data "have" since last post. The variable are 'type' 'value.'
First I need to flag only for type='R'.
Second, if value is blank when visit=scheduled then next obs should be flagged.( Eg; ID=5. With the code iam flagging scheduled visit with variable "value=blank." If the value is blank the next obs should be flagged).
(Eg.ID=6. With the code I am flagging the date=2014-07-30. But the variable "value " for the obs is blank. The date with2014-07-26 should be flagged.
Please help. Thank you
OUTPUT NEEDED:
ID TYPE TEST VISIT VISITWINDOW VALUE DATE FLAG
1 R dbp 1 1 8 2014-07-30 Y
1 R dbp un 1 9 2014-08-10
2 R dbp 1 1 7 2014-07-30
2 R dbp 1 1 8 2014-08-10 Y
3 R dbp un 1 6 2014-07-30
3 R dbp un 1 8 2014-08-10 Y
4 R dbp 1 1 6 2014-07-30 Y
5 R dbp 1 1 . . 2014-07-28
5 R dbp un 1 2 2014-07-27 Y
6 R dbp 1 1 2 2014-07 26 Y
6 R dbp 1 1 . 2014-07-30
1 M dbp 1 1 4 2014-07-30
1 M dbp un 1 3 2014-08-10
data have; input id type $ test$ visit $ visitwindow value vsdtc :yymmdd10.; format vsdtc date9. ; datalines; 1 R dbp 1 1 8 2014-07-30 1 R dbp un 1 9 2014-08-10 2 R dbp 1 1 7 2014-07-30 2 R dbp 1 1 8 2014-08-10 3 R dbp un 1 6 2014-07-30 3 R dbp un 1 8 2014-08-10 4 R dbp 1 1 6 2014-07-30 5 R dbp 1 1 . 2014-07-28 5 R dbp un 1 2 2014-07-27 1 M dbp 1 1 2014-07-30 1 M dbp un 1 2014-08-10 2 M dbp 1 1 2014-07-30 2 M dbp 1 1 2014-08-10 3 M dbp un 1 2014-07-30 3 M dbp un 1 2014-08-10 4 M dbp 1 1 2014-07-30 run;
your code;
data want;
do i=1 by 1 until(last.id);
set have;
by id;
if visit=1 then idx=i;
end;
do j=1 by 1 until(last.id);
set have;
by id;
flag=' ';
if idx=j then flag='Y';
if last.id and missing(idx) then flag='Y';
output;
end;
drop i j idx;
run;
proc print;run;
You are asking too much thing .
data have;
input ID TYPE $ TEST $ VISIT $ VISITWINDOW VALUE DATE : $20.;
cards;
1 R dbp 1 1 8 2014-07-30
1 R dbp un 1 9 2014-08-10
2 R dbp 1 1 7 2014-07-30
2 R dbp 1 1 8 2014-08-10
3 R dbp un 1 6 2014-07-30
3 R dbp un 1 8 2014-08-10
4 R dbp 1 1 6 2014-07-30
5 R dbp 1 1 . 2014-07-28
5 R dbp un 1 2 2014-07-27
6 R dbp 1 1 2 2014-07 26
6 R dbp 1 1 . 2014-07-30
7 M dbp 1 1 4 2014-07-30
7 M dbp un 1 3 2014-08-10
;
run;
data want;
do i=1 by 1 until(last.id);
set have;
by id;
if visit=1 and type='R' and not missing(value) then idx=i;
end;
do j=1 by 1 until(last.id);
set have;
by id;
flag=' ';
if idx=j and type='R' then flag='Y';
if last.id and type='R' and missing(idx) then flag='Y';
output;
end;
drop i j idx;
run;
proc print;run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.