topic Re: Asisst with error on SUM summary function & columns not found in SAS Programming

Asisst with error on SUM summary function & columns not found

sabella_btpg — Sat, 05 Jun 2021 15:36:51 GMT

Hi guys,

I am a newbie to SAS/SQL coding, needed help on 1 of the coding block as below:

proc sql;
create table abc_123 as select
Date-21916 format date9. as sub_date,
"ABCD" as staff,
"ABCDE" as product_name FORMAT=$CHAR200. LENGTH=200,

sum('Total Pieces'n) as xyz,
0 as afyp

from XXX
where Date-21916>=&stdt
and Date-21916<=&eddt

group by 1,2,3
;quit;

The error received:

1. ERROR: The SUM summary function requires a numeric argument.

2. ERROR: The following columns were not found in the contributing tables: Date, 'Total Pieces'n.

Although both of the tables is available in my excel file.

Anybody expertise on this are greatly appreciated.

TQ.

Re: Asisst with error on SUM summary function & columns not found

Tom — Sat, 05 Jun 2021 15:50:42 GMT

Run PROC CONTENTS on XXX to see what variables there are and how they are defined.

proc contents data=xxx;
run;

The NAME attribute is how you will reference the variable. The TYPE attribute determines whether the variable is a fixed length character string or a floating point number. The LENGTH attribute show how many bytes are used to store the value in the SAS dataset. The FORMAT shows if you have asked SAS to use any special logic when displaying the values of that variable as text, like in a report or just writing the values to the log. The LABEL attribute is just for additional description of the variable. Some reports and browsing tools might default to showing the LABEL instead of the NAME, but in code it is the NAME that you need to use.

To use SUM() the variable has to be numeric in the SAS dataset.

Note that Excel does NOT have the concept of a variable type. To Excel everything is an individual cell. So it is possible your Excel file has some numeric and some character values in the same column. In which case SAS will have to define the variable create to hold the value in the column as character.

Re: Asisst with error on SUM summary function & columns not found

jimbarbour — Sat, 05 Jun 2021 15:45:30 GMT

Try this:

First, add this code:

OPTIONS VALIDVARNAME=V7

then change your SQL as follows (I'm modifying the column name of "Total Pieces" to Total_Pieces):

proc sql;
create table abc_123 as select
    Date-21916 format date9. as sub_date,
    "ABCD" as staff,
    "ABCDE" as product_name FORMAT=$CHAR200. LENGTH=200,
    sum(Total_Pieces) as xyz,
    0 as afyp
from XXX
where Date-21916>=&stdt
and Date-21916<=&eddt
group by 1,2,3
;
quit;

Jim

Re: Asisst with error on SUM summary function & columns not found

sabella_btpg — Sat, 05 Jun 2021 16:43:15 GMT

I've run the xxx PROC CONTENTS, the 'xyz' type is Num with '8' Len.
I've also double checked the value of the excel column, it is all in numeric.

Re: Asisst with error on SUM summary function & columns not found

sabella_btpg — Sat, 05 Jun 2021 16:51:58 GMT

Done this, no luck, the same error returned. 😞

Re: Asisst with error on SUM summary function & columns not found

jimbarbour — Sat, 05 Jun 2021 17:05:47 GMT

I would need to see the output of your Proc Contents. There must be some misspelling or something of the column name in order for this to be happening.

I would need to see this portion of your Proc Contents (below). Would you please post this portion of your Proc Contents results?

Jim

Re: Asisst with error on SUM summary function & columns not found

Tom — Sat, 05 Jun 2021 17:16:50 GMT

@sabella_btpg wrote:
I've run the xxx PROC CONTENTS, the 'xyz' type is Num with '8' Len.
I've also double checked the value of the excel column, it is all in numeric.

Then why are you trying to sum a different variable? use the name the variable from the source dataset inside the SUM() aggregate function call. Place the name you want to call the result in the output dataset after the AS keyword. They could be same.

sum(xyz) as xyz

Re: Asisst with error on SUM summary function & columns not found

sabella_btpg — Sat, 05 Jun 2021 17:37:43 GMT

here you go..

cnt = xyz

Re: Asisst with error on SUM summary function & columns not found

sabella_btpg — Sat, 05 Jun 2021 17:41:50 GMT

that's what i've done, the sum('total pieces'n) is the name taken from the source dataset and i want it to ouput as xyz

Re: Asisst with error on SUM summary function & columns not found

jimbarbour — Sat, 05 Jun 2021 17:59:33 GMT

@sabella_btpg wrote:

here you go..

cnt = xyz

OK, so look at the output of your Proc Contents. There is no column named "Total Pieces" (or "Total_Pieces"). You do have a numeric column called "cnt". I think you want to sum on cnt, yes?

You also do not have a column named Date-21916. You do have a column called Prod_Year (numeric) and another called Prod_Mth (character, 3 positions). You may have to figure out a way to combine Prod_Year and Prod_Mth to create a date.

For now, just working with Prod_Year because we haven't yet figured out a way to combine Prod_Year and Prod_Mth, here's about as much as we can do:

proc sql;
create table abc_123 as select
    Prod_Year as sub_date,
    "ABCD" as staff,
    "ABCDE" as product_name FORMAT=$CHAR200. LENGTH=200,
    sum(cnt) as xyz,
    0 as afyp
from XXX
where Prod_Year>=YEAR(&stdt)
and Prod_Year<=YEAR(&eddt)
group by 1,2,3
;
quit;

Why don't you try running the above? Let's see what we get from that. I'm assuming that &stdt and &eddt are valid SAS dates. If they are not valid SAS dates, then the above will not work.

We may need to look at &stdt and &eddt. Would you please share the values of &stdt and &eddt?

Jim

Re: Asisst with error on SUM summary function & columns not found

Kurt_Bremser — Sat, 05 Jun 2021 18:00:28 GMT

@sabella_btpg wrote:

here you go..

cnt = xyz

What do you mean by that? cnt is cnt, period. And I do not see any "Total Pieces" anywhere.

Re: Asisst with error on SUM summary function & columns not found

ballardw — Sat, 05 Jun 2021 20:30:03 GMT

@sabella_btpg wrote:
that's what i've done, the sum('total pieces'n) is the name taken from the source dataset and i want it to ouput as xyz

So what is the variable type for 'total pieces'n from proc contents on the source data set XXX (or what ever the real name is you used XXX above because the name of the data set is secret)