topic Re: Error 22:232 and error 76:232 in SAS Programming

Error 22:232 and error 76:232

Feksan — Sun, 04 Oct 2020 03:29:34 GMT

I have run this code and get an error. What could be the mistake?

SELECT moyenne_globale
CASE WHEN nouveaux_cas>moyenne_globale THEN 1
ELSE 0 end as comparaison_moyenne_globale
FROM d1.COVID_DATA;

but I am getting this error:

SELECT moyenne_globale
70 CASE WHEN nouveaux_cas>moyenne_globale THEN 1
____
22
76
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =,
>, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE,
LEN, LENGTH, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

71 ELSE 0 end as comparaison_moyenne_globale
72 FROM d1.COVID_DATA;

Re: Error 22:232 and error 76:232

novinosrin — Sun, 04 Oct 2020 03:39:34 GMT

SELECT moyenne_globale ,
CASE WHEN nouveaux_cas>moyenne_globale THEN 1
ELSE 0 end as comparaison_moyenne_globale
FROM d1.COVID_DATA;

You are missing--> ,

Re: Error 22:232 and error 76:232

ballardw — Sun, 04 Oct 2020 05:51:36 GMT

If you look at the post of the log you will see that the the message window reformatted the text.

Please post logs by copying text from the log and then pasting into a code box opened on the forum using the </> icon.

You should note that the _ character that appears above the 22 would show the location that SAS determined that your code does not meet the syntax requirements. That may not be the actual location of the error depending on your specific error but that is usually not far. Knowing that the _ indicates where the expected characters should be usually tells what you need.

Re: Error 22:232 and error 76:232

Kurt_Bremser — Sun, 04 Oct 2020 07:40:52 GMT

Proper code formatting goes a long way in revealing problems:

select
  moyenne_globale /* oops, elements in the same sql clause need to be separated by a comma */
  case
    when nouveaux_cas>moyenne_globale
    then 1
    else 0
  end as comparaison_moyenne_globale
from d1.COVID_DATA;

Re: Error 22:232 and error 76:232

Feksan — Sun, 04 Oct 2020 15:07:42 GMT

Actually this is the whole code and when I run I always have those errors. Do you see a mistake?

proc sql;

CREATE TABLE d1.COVID AS
SELECT*,
FROM d1.owid_covid_data_2019
UNION ALL
SELECT*,
FROM d1.owid_covid_data_2020
;
quit;

proc sql;

CREATE TABLE d1.COVID_DATA AS
SELECT A.iso_code,
A.continent,
A.location AS pays,
B.date,
B.new_cases AS nouveaux_cas,
B.new_deaths AS nouveaux_deces,
B.new_tests AS nouveaux_tests,
B.total_tests AS nb_total_tests,
B.population,
B.female_smokers AS femmes_fumeuses,
B.male_smokers AS hommes_fumeurs
FROM d1.iso_code_table AS A
INNER JOIN d1.COVID AS B
ON A.iso_code = B.iso_code;

SELECT*, date format yymmn6. as month,
put(date,yyq6.) as quarter
FROM d1.COVID_DATA;

SELECT*, sum(nouveaux_tests) AS test_par_population,
FROM d1.COVID_DATA
GROUP BY population;

DELETE FROM d1.COVID_DATA
WHERE iso_code="owid_wrl";

SELECT sum(nouveaux_cas)/count(nouveaux_cas) AS moyenne_globale,
FROM d1.COVID_DATA;

SELECT moyenne_globale,
CASE WHEN nouveaux_cas>moyenne_globale THEN 1
ELSE 0 end as comparaison_moyenne_globale
FROM d1.COVID_DATA;

SELECT*,
CASE WHEN hommes_fumeurs>femmes_fumeuses THEN "Hommes_plus_Femmes"
WHEN hommes_fumeurs<femmes_fumeuses THEN "Hommes_moins_Femmes"
WHEN hommes_fumeurs=femmes_fumeuses THEN "Hommes_egale_Femmes"
ELSE "NA" end as comparaison_sex
FROM d1.COVID_DATA;

quit;

Re: Error 22:232 and error 76:232

PaigeMiller — Sun, 04 Oct 2020 15:19:39 GMT

@Feksan wrote:

Actually this is the whole code and when I run I always have those errors. Do you see a mistake?

proc sql;

CREATE TABLE d1.COVID AS
SELECT*,
FROM d1.owid_covid_data_2019
UNION ALL
SELECT*,
FROM d1.owid_covid_data_2020
;
quit;

You might want to seriously consider the suggestion from @Kurt_Bremser to format your code properly.

You might want to show us the LOG as explained by @ballardw . Saying you get errors is not the same as SHOWING us the errors in the LOG.

All of these things will help YOU get answers faster.

I believe you get errors because of a misplaced comma. A comma is used to separate variables in a SELECT clause, it should not be in your code because you are not separating variables. There might be other errors, but we don't know because you haven't shown us the LOG in the required format.

proc sql;
CREATE TABLE d1.COVID AS
SELECT*, /* There should be NO comma on this line and please put a space after SELECT */
FROM d1.owid_covid_data_2019
UNION ALL
SELECT*,  /* There should be NO comma on this line and please put a space after SELECT */
FROM d1.owid_covid_data_2020
;
quit;

What mistakes am I making in the sql code with sas because I get the Errors 22 and 76?

Feksan — Sun, 04 Oct 2020 15:28:53 GMT

proc sql;

CREATE TABLE d1.COVID AS
SELECT*,
FROM d1.owid_covid_data_2019
UNION ALL
SELECT*,
FROM d1.owid_covid_data_2020
;
quit;

proc sql;

SELECT*, date format yymmn6. as month,
put(date,yyq6.) as quarter
FROM d1.COVID_DATA;

SELECT*, sum(nouveaux_tests) AS test_par_population,
FROM d1.COVID_DATA
GROUP BY population;

DELETE FROM d1.COVID_DATA
WHERE iso_code="owid_wrl";

SELECT sum(nouveaux_cas)/count(nouveaux_cas) AS moyenne_globale,
FROM d1.COVID_DATA;

SELECT moyenne_globale,
CASE WHEN nouveaux_cas>moyenne_globale THEN 1
ELSE 0 end as comparaison_moyenne_globale
FROM d1.COVID_DATA;

quit;`

`ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?,
AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH,
LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.`

Re: What mistakes am I making in the sql code with sas because I get the Errors 22 and 76?

PaigeMiller — Sun, 04 Oct 2020 15:31:28 GMT

Please don't post the same question twice.

This has been answered in your other thread at https://communities.sas.com/t5/SAS-Programming/Error-22-232-and-error-76-232/td-p/688734

Re: Error 22:232 and error 76:232

Kurt_Bremser — Sun, 04 Oct 2020 15:42:24 GMT

I guess some tuition on the basics of SQL coding is on order.

When you have a list of similar objects (columns or tables) within a clause, these objects must be separated by a comma; if only one object is used (the asterisk counts as one), then no comma must be used.

And you need to separate objects from keywords by at least one blank.

So this is wrong:

SELECT*,
FROM d1.owid_covid_data_2019

There is no blank separating the asterisk from the keyword SELECT;

if there were, there would be only one object in the SELECT, so the comma must not be used.

To aid in giving you help, post the log of a failing step by copy/pasting into a window opened with the </> button.

Re: What mistakes am I making in the sql code with sas because I get the Errors 22 and 76?

Kurt_Bremser — Sun, 04 Oct 2020 15:47:25 GMT

I merged this back into the original thread.

Re: Error 22:232 and error 76:232

Tom — Sun, 04 Oct 2020 19:50:28 GMT

You need to put commas in between variables in a list in SQL (unlike in normal SAS code where you use spaces). N items will require N-1 commas. So if there are three items you need two commas. If there is only one item then you need zero commas.

SELECT *
FROM d1.owid_covid_data_2019
...

If will help you limit the number of times you make mistakes with missing/added commas if you get in the habit placing the commas at the beginning of the continuation line instead of at the end of the line you are wrapping. This will make it easier for you to notice when scanning the code since you don't need to move your focus left and right.

SELECT
    A.iso_code
  , A.continent
  , A.location AS pays
...