using export excel in combination with conditional statement

[Moniek Bresser]

Dear all,

Would you be able to advise me on the following?

I have written several checks to flag data inconsistencies (e.g. are there duplicate subject ids in my data set?)

If such a check finds any inconsistencies (at least one observation), I want to export these cases to Excel, so I can share these observations with the colleague that is responsible for collecting and cleaning the data.

Stata will stop running my code if the inconsistency check finds no observations ("Too many or no variables specified"). However, I have about 20 of these inconsistency checks and it is time consuming to tell Stata to continue running the rest of the code every time it does not find any observations in an inconsistency check.

So what I would like is for the code below to run only if there is at least one observation to export and if there are no observations to continue running my dofile instead of stopping and telling me "Too many or no variables specified")

I have tried:

(A):
export excel using "$data/data_inconsistency_check1.xls", replace firstrow(var) if _n > 0
--> Result: Unfortunately export excel does not accept if statements

(B):

foreach i in subjectid {
export excel using "$data/data_consistency_check1.xls", replace firstrow(var)
}
--> Result: this gives me the same "Too many or no variables specified" message when there are no observations to export, and if there would be more than 1 observation, the export would be performed multiple times, so this would not be a suitable solution to be honest.

Thank you kindly for your ideas and suggestions,

Best wishes,

Moniek

[William Lisowski]

Rather than answer your question about export excel, let me first address

Stata will stop running my code if the inconsistency check finds no observations

The output of help capture and the full PDF documentation linked to discusses using the capture prefix to prevent Stata from stopping.

With that said, I think the following is a version of (A) that will not fail when there are no observations in your dataset to export.

if c(N) > 0 {
    export excel using "$data/data_inconsistency_check1.xls", replace firstrow(var)
}

where the output of help creturn explains the meaning of c(N) and other useful system values similarly accessible.

[Mike Lacy]

I'd agree with William that -capture- could be a useful tool for problems like yours.

However, without seeing your code or a sample of your data (see previous successfully answered questions on this forum for examples), you're not very like to get a more detailed answer. Take a look at the FAQ about such things as -dataex- and using code delimiters to display code and data. That aside, from what you have said here, my suggestions would be:

1) Your "export ...if ...." doesn't do what you want because, among other things, it confuses Stata's "if qualifier" with the "if command." This is an admittedly unusual feature of Stata. Take a look at -help ifcmd- and -help if- for starters. This may or may not turn out to be helpful for your current problem, but it would be helpful to you otherwise.

2) I would approach your problem, such as I understand it, by computing a series of variables that indicate for each observation, whether it display the types of inconsistencies to which you refer, e.g., problem1, problem2, problem3. (1 = data problem of this type is present in this observation, 0 if not.)

Then, you could do something like this:

generate problem1 = 0
generate problem2 = 0
generate problem3 = 0
... run consistency checks and set problem1 etc. to 1 for any observation in which that type of problem exists
 ... 
keep if inlist(1, problem1, problem2, problem3)
export using .....   (no if needed in the export command)

However, this suggestion is a complete guess, since I don't have any idea of the structure of your data.

[Moniek Bresser]

Thank you William and Mike for your advice.

-capture- is exactly what I needed, I knew there must be a way to become more efficient in running data quality checks!

Also interesting to see that there is a way to export data based on an if statement after all. I am still quite stuck in the mindset that the action is mentioned first and then the 'if', e.g. replace if ......, and did not think of starting with the -if- statement and then running the -export excel-. I will dive into the suggested help sections you suggested to increase my knowledge on this.

@Mike: My apologies for not providing an idea of the structure of my data, I did this deliberately as I assumed the solution could run irrespective of my data structure.

Thank you and best wishes,

Moniek