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.
@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,
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,)
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.
Hi @Jonison Could you please post a sample of your input data and your expected output?
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.
@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,
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;
@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.
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 25. 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.