topic Re: SAS last. query modification in SAS Programming

SAS last. query modification

GunnerEP — Tue, 14 Feb 2017 06:52:35 GMT

I need help with the below code snippet.

data A dup;
set B; /* Sorted dataset by id update_dt updt_code*/
by id update_dt updt_code;
if first.id and last.updt_code then output A;
else output dup;
run;

Data B:

id Update_dt updt_code
123 14/02/2017 3

123 14/02/2017 3
123 14/02/2017 6

Required Data A:
id Update_dt updt_code
123 14/02/2017 3
123 14/02/2017 6

Re: SAS last. query modification

Kurt_Bremser — Tue, 14 Feb 2017 07:15:07 GMT

The most efficient way to achieve your intended (as I guess) result is this IMO:

proc sort
  data=b
  out=a
  nodupkey
  dupout=dup
;
by id update_dt updt_code;
run;

Re: SAS last. query modification

mkeintz — Tue, 14 Feb 2017 07:16:18 GMT

by filtering on "first.id" you can only get at most the first of your three recrods. But the last.updt_code dummy is not simultaneously true in your data. I think you want;

data a dup;

set b;

by id update_dt updt_code;

if first.updt_code=1 and last.updt_code=1 then output a;

else output dup;

run;

Remember, if you have multiple BYvars, whenever a given by var is in first. or last. condition, all by vars to its right will also be set to first. or last. condition - even if their values don't change throughout the entire dataset. By-variable dummies are hierarchical.