SUM function sometimes works and sometimes doesn't

[BillB007]

I am importing a lot of Excel files and creating reports.  One of the things I have to do is to sum the values of a variable.  The Sum function works with many of the files but some don't.   The errors I get on some of them are similar to this sample:  

"ERROR: The SUM summary function requires a numeric argument.
ERROR: The following columns were not found in the contributing tables: L5."

I've checked the datasets with Proc Contents and in every one one that throws this error, that variable is numeric.

Any ideas why I'm having this problem and more importantly, how to solve it?

relevant SAS code below.  This is all inside of a set of macros.  The purpose of this macro is to see if all the values of the variable L5 are zero.  If they are, I create a text variable with L5 in it, and later use it in a DROP statement to drop the variable from the dataset.

%macro checkL5(source); 
%global L5total;
%global DROPLIST;
proc sql;
select sum(L5)into :L5total
from &source;
quit;
%IF &L5total =0 %then %LET DROPLIST = L5;

%mend checkL5;

I've tried this on SAS 9.2 and also with SAS 9.4 with the same results.

Thanks!

Bill

[Reeza]

Post the proc contents, code and log.

99% of the time it's user error, not an error with SAS, so double check your work. 

[BillB007 · Accepted solution]

I discovered why. It was trying to sum data in a variable that didn't exist. I just have to test for the existence of that variable first. User error confirmed!

[RW9]

Well, I would be pretty confident in saying that eithr your data isn't the way you think it is.  If your importing lots of Excel files - this alone will cause you many headaches.  Excel is not a good data store, it has no structure, and I suppose your using proc import?  If so that is a guessing procedure, it guesses what your data is, this alone is not a good setup.  Now onto your code.  You have lots of datafiles from the Excel files yes, why not process them into one dataset, this will make your further coding far easier than having to create macro loops.

%macro checkL5(source); 
  %global L5total;
  %global DROPLIST;
  proc sql;
    select sum(L5)
    into :L5total
    from &source;
  quit;
  %IF &L5total =0 %then %LET DROPLIST = L5;
%mend checkL5;

Now, looking at the above, what is &SOURCE. ?  Have you put options mlogic symbolgen mprint; on and seen exatcly what dataset is being used there?  Why are you creating global macro variables inside a macro, not sure this is a good use of scoping.  Also, why do you need to put the sum into a macro variable?  Why not use Base SAS procedures?  For further code, post some example test data (datastep) of the data you have and what you want to get to as it sounds like your making it a bit more complicated than it needs to be.