DATA Step, Macro, Functions and more

How to sort and find an obs and flag the obs

Reply
Super Contributor
Posts: 272

How to sort and find an obs and flag the obs

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

 

Valued Guide
Posts: 634

Re: How to sort and find an obs and flag the obs

[ Edited ]
Posted in reply to knveraraju91

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;
Super Contributor
Posts: 272

Re: How to sort and find an obs and flag the obs

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.

Valued Guide
Posts: 634

Re: How to sort and find an obs and flag the obs

Posted in reply to knveraraju91
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?
Super User
Posts: 5,518

Re: How to sort and find an obs and flag the obs

Posted in reply to knveraraju91

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;

Super User
Posts: 7,863

Re: How to sort and find an obs and flag the obs

Posted in reply to knveraraju91

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 10,046

Re: How to sort and find an obs and flag the obs

Posted in reply to knveraraju91
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;
Super Contributor
Posts: 272

Re: How to sort and find an obs and flag the obs

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; 

 

Super User
Posts: 10,046

Re: How to sort and find an obs and flag the obs

Posted in reply to knveraraju91

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;
Ask a Question
Discussion stats
  • 8 replies
  • 206 views
  • 6 likes
  • 5 in conversation