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
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: