topic Re: prog sql summary function in SAS Programming

prog sql summary function

Jonison — Fri, 16 Aug 2019 15:37:19 GMT

PROC SQL;
	SELECT quote(BAT_NUM) into: ListFP separated by ',' FROM EMWS6.EMCODE2_TRAIN;
   /*CREATE TABLE EMWS6.EMCODE_TRAIN AS*/
	CREATE TABLE TLIST AS 
   SELECT t1.'ResultBelow0.440µm'n,
			 avg(t1.'ResultBelow0.440µm'n,) as AvgResultBelow0_400,
			 t1.BatchNumber
      FROM GDM_DIST.VW_M2000_VALID_DATA t1
      WHERE t1.BatchNumber IN (&ListFP)
		group by t1.BatchNumber; 
	SELECT * FROM TLIST;
QUIT;

Results showed:

ERROR: Function AVG could not be located.
WARNING: A GROUP BY clause has been transformed into an ORDER BY clause because neither the SELECT clause nor the optional HAVING clause of the associated table-expression referenced a summary function.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

Any suggestions are very appreciated.

Re: prog sql summary function

novinosrin — Fri, 16 Aug 2019 15:43:30 GMT

Is it perhaps a syntax issue i,e

  SELECT t1.'ResultBelow0.440µm'n,
			 avg(t1.'ResultBelow0.440µm'n,) as AvgResultBelow0_400,
			 t1.BatchNumber

should not have a comma after 'n in avg(t1.'ResultBelow0.440µm'n,) /*try removing this comma*/

Also I am not sure of your logic to have the same varnames

 SELECT t1.'ResultBelow0.440µm'n,
			 avg(t1.'ResultBelow0.440µm'n,)

Re: prog sql summary function

Jonison — Fri, 16 Aug 2019 17:03:23 GMT

Many thanks for your kind reply.
The comma has been removed, and the message shows:

ERROR: The AVG summary function requires a numeric argument.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

I want to average the column to get average value of that column as a new variable, and only numeric data is in columns.

Re: prog sql summary function

novinosrin — Fri, 16 Aug 2019 17:16:53 GMT

Hi @Jonison Could you please post a sample of your input data and your expected output?

Re: prog sql summary function

Reeza — Fri, 16 Aug 2019 17:20:38 GMT

It may look like a number but if the variable type is not numeric it cannot be used as a number. Verify your data types and go back and clean up your data input step.

Re: prog sql summary function

Jonison — Fri, 16 Aug 2019 17:51:34 GMT

thanks for your kind reply.

Result
Below0.440µm	BatchNumber

0.062	3G5A
0.077	3G5A
0.062	3G5A
0.073	3G5A
0.062	3G5A
0.074	3G5A
0.172	L29S
0.28	L29S
0.274	L29S
0.178	L29S
0.34	L35P
0.297	L35P
0.335	L35P
0.303	L35P

This is the data extracted from our database, the data was grouped by batch number. I would like to use average value to summarize the data. So each batch will only have one average value.

Many thanks for your help in advance.

Re: prog sql summary function

PaigeMiller — Fri, 16 Aug 2019 18:00:49 GMT

@Jonison wrote:

thanks for your kind reply.

Result

Below0.440µm BatchNumber

0.062 3G5A

0.077 3G5A

0.062 3G5A

0.073 3G5A

0.062 3G5A

0.074 3G5A

0.172 L29S

0.28 L29S

0.274 L29S

0.178 L29S

0.34 L35P

0.297 L35P

0.335 L35P

0.303 L35P

This is the data extracted from our database, the data was grouped by batch number. I would like to use average value to summarize the data. So each batch will only have one average value.

Many thanks for your help in advance.

When someone in this forum (in this case @Reeza) asks you to go back and verify that the variable type is (or is not) numeric, we need you to look at PROC CONTENTS or the DICTIONARY table of the specific SAS data set or of the database table, and see if the variable in question is shown as having a type of numeric or character. Simply displaying the data as you have done isn't really the answer we are looking for.

However, since you are getting an error, it seems clear that the values in the first column (such as 0.062) are characters, and so you can't take an average of characters.

You need to convert these to numerics, one way to do this is via the INPUT function. Then you can perform mathematical calculations on it.

avg(input(t1.'ResultBelow0.440µm'n,best12.)) as AvgResultBelow0_400,

Re: prog sql summary function

Tom — Fri, 16 Aug 2019 18:45:57 GMT

Doesn't look like a numeric value to me. Why is it left aligned instead of right aligned. Why don't all of the values show the same number of decimal places?

data test;
  input x y :$10. ;
cards;   
0.062 3G5A
0.077 3G5A
0.062 3G5A
0.073 3G5A
0.062 3G5A
0.074 3G5A
0.172 L29S
0.28 L29S
0.274 L29S
0.178 L29S
0.34 L35P
0.297 L35P
0.335 L35P
0.303 L35P
;
proc print;
run;

Re: prog sql summary function

hashman — Sun, 18 Aug 2019 03:25:20 GMT

@Jonison :

Proc SQL, when presented with using the AVG function, first checks the data type of the column AVG is used with. If the type isn't numeric, it doesn't compile. The message you've received tells you this is the case. SQL doesn't care whether or not the actual characters in the column represent what can be interpreted as a number and, unlike the DATA step, doesn't attempt an implicit conversion. You have to do the conversion yourself using the INPUT function, as suggested by @PaigeMiller.

If you want SAS to convert implicitly (and print the note to this effect in the log), you have to do it in the DATA step. However, in my opinion, letting any implicit conversions to occur is bad programming practice. SQL is just more rigid about this and just fails to run if the data type is wrong, which, given how I feel about implicit conversions, is a good thing. I'd much prefer the DATA step, too, to fail at the compilation stage if the data type of a variable fed to a numeric or character function isn't numeric or character, correspondingly. It would deter many SAS users from writing sloppy code and littering the logs with gazillions of data type conversion notes.

Kind regards

Paul D.