BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BillB007
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
BillB007
Obsidian | Level 7
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!

View solution in original post

3 REPLIES 3
Reeza
Super User

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
Obsidian | Level 7
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
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 4339 views
  • 0 likes
  • 3 in conversation