topic Re: finding missing items in consecutive order in SAS Procedures

finding missing items in consecutive order

deleted_user — Thu, 10 Jul 2008 18:11:23 GMT

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!

Re: finding missing items in consecutive order

Olivier — Fri, 11 Jul 2008 08:18:08 GMT

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

Re: finding missing items in consecutive order

deleted_user — Fri, 11 Jul 2008 20:36:25 GMT

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

Re: finding missing items in consecutive order

Olivier — Sat, 12 Jul 2008 10:47:32 GMT

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

Olivier

Re: finding missing items in consecutive order

deleted_user — Wed, 16 Jul 2008 15:28:06 GMT

Thanks so much! Much appreciated!