BookmarkSubscribeRSS Feed
knveraraju91
Barite | Level 11

Dear,

 

I need to flag the OBS based on a sort order. Is there a way to flag the prior OBS with non blank value if a blank value is present  for the obs. Eg. For ID=3, in my output i am flagging last OBS which is blank value. In my output,  i need to skip it and flag the prior non blank OBS .Please help . Thank you.

 

data one;


input id visit test $ val ptnum date $19.;
datalines;
1 1 temp 88 2 2010-04-27
1 1 temp 90 2 2010-04-28
1 1 temp 92 2 2010-04-29
2 1 temp 100 2 2010-05-27
2 1 temp 98 2 2010-05-28
2 1 temp 96 2 2010-05-29
3 1 temp 100 2 2010-05-27
3 1 temp 98 2 2010-05-28
3 1 temp . 2 2010-05-29
;
proc sort data=one;
by id test visit ptnum date;
run;
data two;
set one;
by id test visit ptnum date;
if last.ptnum then flag='Y';
run;

output need;

1 1 temp 88 2 2010-04-27        
1 1 temp 90 2 2010-04-28
1 1 temp 92 2 2010-04-29     Y
2 1 temp 100 2 2010-05-27
2 1 temp 98 2 2010-05-28
2 1 temp 96 2 2010-05-29      Y
3 1 temp 100 2 2010-05-27
3 1 temp 98 2 2010-05-28       Y
3 1 temp . 2 2010-05-29

 

OUTPUT GETTING

1 1 temp 88 2 2010-04-27
1 1 temp 90 2 2010-04-28
1 1 temp 92 2 2010-04-29     Y
2 1 temp 100 2 2010-05-27
2 1 temp 98 2 2010-05-28
2 1 temp 96 2 2010-05-29       Y
3 1 temp 100 2 2010-05-27
3 1 temp 98 2 2010-05-28
3 1 temp . 2 2010-05-29          Y

5 REPLIES 5
Astounding
PROC Star

Probably the easiest way is to sort differently:

 

proc sort data=one;

by id test visit ptnum descending date;
run;
data two;
set one;
by id test visit ptnum descending date;

retain ever_flagged;

if first.ptnum then ever_flagged=0;

if val > . then ever_flagged + 1;

if ever_flagged=1 then do;

   flag='Y';

   ever_flagged + 1;

end;

drop ever_flagged;

run;

 

If you would like, you can always put the data back into the order you want:

 

proc sort data=two;
by id test visit ptnum date;
run;

Ksharp
Super User

data one;
input id visit test $ val ptnum date $19.;
datalines;
1 1 temp 88 2 2010-04-27
1 1 temp 90 2 2010-04-28
1 1 temp 92 2 2010-04-29
2 1 temp 100 2 2010-05-27
2 1 temp 98 2 2010-05-28
2 1 temp 96 2 2010-05-29
3 1 temp 100 2 2010-05-27
3 1 temp 98 2 2010-05-28
3 1 temp . 2 2010-05-29
;

data want;
 do i=1 by 1 until(last.test);
  set one;
  by id visit test;
  if not missing(val) then n=i;
 end;
 
 do j=1 by 1 until(last.test);
  set one;
  by id visit test;
  flag=' ';
  if j=n then flag='Y';
  output;
 end;
 
 drop n i j;
run;


RW9
Diamond | Level 26 RW9
Diamond | Level 26

Another option:

data one;
  input id visit test $ val ptnum date $19.;
datalines;
1 1 temp 88 2 2010-04-27
1 1 temp 90 2 2010-04-28
1 1 temp 92 2 2010-04-29
2 1 temp 100 2 2010-05-27
2 1 temp 98 2 2010-05-28
2 1 temp 96 2 2010-05-29
3 1 temp 100 2 2010-05-27
3 1 temp 98 2 2010-05-28
3 1 temp . 2 2010-05-29
;
run;

proc sql;
  create table WANT as
  select  A.*,
          B.FLAG
  from    WORK.ONE A
  left join (select ID,
                    VISIT,
                    TEST,
                    PTNUM,
                    max(input(DATE,yymmdd10.)) as MDATE,
                    "Y" as FLAG
             from   (select * from WORK.ONE where VAL ne .)
             group by ID,VISIT,TEST,PTNUM) B
  on      A.ID=B.ID
  and     A.VISIT=B.VISIT
  and     A.TEST=B.TEST
  and     A.PTNUM=B.PTNUM
  and     A.DATE=put(MDATE,yymmdd10.);
quit;
                     
Genki_Tsuji
Calcite | Level 5

Hellow, it wer me, I would do it like this

 

 

 

data one;


input id visit test $ val ptnum date $19.;
datalines;
1 1 temp 88 2 2010-04-27
1 1 temp 90 2 2010-04-28
1 1 temp 92 2 2010-04-29
2 1 temp 100 2 2010-05-27
2 1 temp 98 2 2010-05-28
2 1 temp 96 2 2010-05-29
3 1 temp 100 2 2010-05-27
3 1 temp 98 2 2010-05-28
3 1 temp . 2 2010-05-29
;

run ;

/* Insert step1 */
data ONE ;
  set ONE ;
  DUMMY_DATE = ^(missing(VAL)) * input(DATE, yymmdd10.) ;
run ;

proc sort data=one;
by id test visit ptnum DUMMY_DATE;
run;
data two(drop=DUMMY_DATE);
set one;
by id test visit ptnum DUMMY_DATE;
if last.ptnum then flag='Y';
run;

 

/* Insert step2 */
proc sort data=two;
by id test visit ptnum date;
run;

mkeintz
PROC Star

First, I try to resist the atavistic impulse to sort a data set by descending order as a means of looking ahead, especially if you're going to re-sort by ascending order - very expensive for large data sets.

 

You want to flag the last non-missing record for each by group:

 

data v_need / view=v_need;

  set one;

  by id test visit ptnum;

  where val^=.;

  if last.ptnum;

  flag='Y';

run;

 

data two;

   merge one v_need;

   by id test visit ptnum date;

run;

 

** This looks like two passes through the data, but V_NEED is a data set view, not a data set file.  That means it is not written to disk, and is activated until called in the DATA TWO step - at which point it is streamed to the data two step.  As a result, in data two, there wlil be two simultaneous streams of input from dataset ONE.  In turn that means that ONE is physically being accessed only once, because the disk input blocks for one stream will be cached in memory and used by the other stream, all thanks to your operating system.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

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
  • 5 replies
  • 850 views
  • 5 likes
  • 6 in conversation