CODEWITHSUNDEEP
sas
InPhamouZ
InPhamouZ

Reputation: 51

SAS: how can I find the missed/skipped number in a sequence?

I have a dataset and one column is the ID column which has a sequential of numbers Exmaple:
ID
1
3
5
6
7
8
10
so it's missing 2, 4 and 9 in SAS how can I code it to find these missed/skipped numbers?
I tried Google, but mostly just missing values, etc.

Any help would be much appreciated!

Thanks!

Upvotes: 0

Views: 294

Answers (2)

Richard
Richard

Reputation: 27508

Try

data missing_ids;
  set have;
  do id = coalesce(lag(id),0)+1 to id-1;
    output;
  end;
run;

These are the features of SAS DATA Step that make this example work:

  • DATA Step implicit loop, reads one observation at SET statement
  • DO Statement: Iterative, start TO stop
    • The value of start is evaluated before the first execution of the loop
    • The value of stop is evaluated (once) before the first execution of the loop
  • LAG(id) in context of example returns value of id from prior observation. Technically, the LAG<n>() function returns a value from a LIFO queue of <n> items.
    • When at first observation, LAG(id) returns missing.
    • COALESCE returns first nonmissing value from the list of values passed to it. That is why COAELSCE is used to ensure first start is nominally 0+1
    • SUM could also be used because SUM function returns the sum of the nonmissing values

LAG can be subtle and should be used inside conditional blocks only if you know what you are doing.

Upvotes: 4

Kermit
Kermit

Reputation: 3117

Replicate the example

data have;
input id;
cards;
1
3
5
6
7
8
10
;

Select the minimum and maximum value of the id column into two macro variables min and max then compute the sequence in the numbers table

proc sql noprint;
    select min(id), max(id) into :min, :max from have;
quit;

data numbers;
    do id=&min. to &max. by 1;
        output;
    end;
run;

Get the missing numbers in table want

proc sql;
    create table want as
    select id from numbers
    where id not in (select id from have);
quit;

id
2
4
9

Upvotes: 1

Related Questions