topic Re: Find and recognize the max(date)record from a random list of records in SAS Programming

Find and recognize the max(date)record from a random list of records

heraldic2 — Wed, 04 May 2022 18:42:11 GMT

Greetings,

I am trying to update an existing record set so I can update a flag for the most recent entry that can be used for reporting purposes and I have hit a wall and need help.

I have, and cannot alter, data in this format although it is a bit more random in the order:

data work.records; input ToolID Date $ Outcome $ Flag; datalines; 12345 01/01/2022 Pass 0 12345 04/01/2022 Fail 0 12345 02/01/2022 Fail 0 12345 12/01/2021 Pass 0 12345 03/01/2022 Pass 0 98765 03/01/2022 Fail 0 98765 02/01/2022 Pass 0 65471 02/01/2022 Fail 0 ; run;

What I need to do is update the flag to be equal to 1 where it is the records max(date)

data work.records; input ToolID Date $ Outcome $ Flag; datalines; 12345 01/01/2022 Pass 0 12345 04/01/2022 Fail 1 12345 02/01/2022 Fail 0 12345 12/01/2021 Pass 0 12345 03/01/2022 Pass 0 98765 03/01/2022 Fail 1 98765 02/01/2022 Pass 0 65471 02/01/2022 Fail 1 ; run;

I have tried creating a temp table and hold the max date and tool id, but I keep hitting a wall on how to update the flag. Nothing I have tried has been successful at doing the update, but that makes sense as I am only so so when it comes to SAS programming and I tend to use the PROC SQL more then anything else.

There has to be a simple way to do this! HELP!!

Re: Find and recognize the max(date)record from a random list of records

Kurt_Bremser — Wed, 04 May 2022 19:18:34 GMT

Use a double DO loop:

data want;
do until (last.toolid);
  set have;
  by toolid;
  maxdate = max(maxdate,date);
end;
do until (last.toolid);
  set have;
  by toolid;
  if date = maxdate then flag = 1;
  output;
end;
drop maxdate;
run;

Re: Find and recognize the max(date)record from a random list of records

PaigeMiller — Wed, 04 May 2022 19:02:09 GMT

data work.records;
    input ToolID Date :mmddyy10. Outcome $;
    datalines;
12345 01/01/2022 Pass 0
12345 04/01/2022 Fail 0
12345 02/01/2022 Fail 0
12345 12/01/2021 Pass 0
12345 03/01/2022 Pass 0
98765 03/01/2022 Fail 0
98765 02/01/2022 Pass 0
65471 02/01/2022 Fail 0
;
run; 

proc sql;
    create table want as select *,date=max(date) as flag
    from records
    group by toolid;
quit;

Re: Find and recognize the max(date)record from a random list of records

heraldic2 — Wed, 04 May 2022 20:19:07 GMT

Thank you very much. I was able to get this to work quite easily and learned about the MAX() function.