topic Re: How to sort and find an obs and flag the obs in SAS Programming

How to sort and find an obs and flag the obs

knveraraju91 — Fri, 24 Mar 2017 06:03:41 GMT

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

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

ArtC — Fri, 24 Mar 2017 06:40:13 GMT

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;

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

knveraraju91 — Fri, 24 Mar 2017 06:46:03 GMT

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.

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

Astounding — Fri, 24 Mar 2017 07:13:40 GMT

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;

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

Kurt_Bremser — Fri, 24 Mar 2017 07:14:46 GMT

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.

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

Ksharp — Fri, 24 Mar 2017 11:01:37 GMT

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;

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

ArtC — Fri, 24 Mar 2017 17:48:14 GMT

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?

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

knveraraju91 — Sat, 25 Mar 2017 00:08:10 GMT

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;

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

Ksharp — Sat, 25 Mar 2017 04:34:53 GMT

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;