BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

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

 

8 REPLIES 8
ArtC
Rhodochrosite | Level 12

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;
knveraraju91
Barite | Level 11

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.

ArtC
Rhodochrosite | Level 12
for ID 1, i am getting the flag on the correct observation. in your test did you have a numeric visit number? if so are you using an ascii machine?
Astounding
PROC Star

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;

Kurt_Bremser
Super User

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.

Ksharp
Super User
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;
knveraraju91
Barite | Level 11

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; 

 

Ksharp
Super User

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;

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 Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1087 views
  • 6 likes
  • 5 in conversation