02-26-2016 10:31 AM
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.
select sum(L5)into :L5total
%IF &L5total =0 %then %LET DROPLIST = L5;
I've tried this on SAS 9.2 and also with SAS 9.4 with the same results.
02-26-2016 01:56 PM
02-26-2016 01:56 PM
02-26-2016 10:52 AM
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.
Need further help from the community? Please ask a new question.