Help using Base SAS procedures

finding missing items in consecutive order

Reply
N/A
Posts: 0

finding missing items in consecutive order

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!
Super Contributor
Posts: 260

Re: finding missing items in consecutive order

Posted in reply to deleted_user
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
N/A
Posts: 0

Re: finding missing items in consecutive order

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
Super Contributor
Posts: 260

Re: finding missing items in consecutive order

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

Olivier
N/A
Posts: 0

Re: finding missing items in consecutive order

Thanks so much! Much appreciated!
Ask a Question
Discussion stats
  • 4 replies
  • 145 views
  • 0 likes
  • 2 in conversation