DATA Step, Macro, Functions and more

SAS last. query modification

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

SAS last. query modification

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


Accepted Solutions
Solution
‎02-14-2017 02:25 AM
Super User
Posts: 7,832

Re: SAS last. query modification

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎02-14-2017 02:25 AM
Super User
Posts: 7,832

Re: SAS last. query modification

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,022

Re: SAS last. query modification

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 120 views
  • 1 like
  • 3 in conversation