finding missing items in consecutive order

[deleted_user]

Hi,

I have a data set which should have all of the following in consecutive order:

Q001
Q002
Q003
Q004
'
'
'
Q180

How do I find which ones are missing, for example, if Q 175 is missing?
I tried to do a proc sort, then a proc freq, by this variable, but it's labor intensive to look through each one and determine if one is missing.


Thanks!

[Olivier]

Hi.
I suggest you sort and deduplicate the values, then compare the number to the previous one in a datastep ; each time the difference is > 1, you print a message.
[pre]
PROC SORT DATA = myData OUT = myValues NODUPKEY ;
BY myVariable ;
RUN ;
DATA _NULL_ ;
SET myValues ;
previousVal = LAG(myVariable) ;
IF _N_>1 THEN DO ;
gap = SUBSTR(myVariable,2) - SUBSTR(previousVal, 2) ;
nbMissing = gap-1 ;
IF nbMissing > 0 THEN PUT nbMissing "missing value(s) between " previousVal " and " myVariable ;
END ;
RUN ;
[/pre]
Regards.
Olivier

[deleted_user]

This works very well! Thanks so much!
Is it possible to print the list in the output (vs. the log)? Thanks! Message was edited by: jcis

[Olivier]

Of course you can display the list in the Output.
Just add the line :
[pre]
FILE PRINT ;
[/pre]
after the DATA _NULL_ ; statement.

Olivier

[deleted_user]

Thanks so much! Much appreciated!