ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant,

[fadamso12]

Hello, everyone. Can someone help me fix this code? I am trying to summarize data by the following variables: age group, county, year admitted admissionmonth, race, and sex. Below is a snippet of the data. 

proc sql number;
create table rr as 
select distinct AdmissionMonth, AgeGroup, County, Ethnicity, Race, HealthOutcomeID, MonthlyHosp, 
Sex, ADYEAR,
count (case when ASTHMA_FLAG=1 
from count_Asthma
group by AdmissionMonth, AgeGroup, County, Ethnicity, Race, HealthOutcomeID, MonthlyHosp, 
		Sex, ADYEAR; 
quit;

[PaigeMiller]

You are missing a right parenthesis. And even if you fix that, your CASE WHEN is incomplete.

[fadamso12]

@PaigeMiller, if you don't mind, can you tell me the syntax? I have never created a table using SQL before, so I am trying it for the first time. 

[PaigeMiller]

From code that doesn't work, I would need to know what this SQL is supposed to do in order to get it to work, especially the part where you get the error, what are you trying to do?

[Tom]

First thing is you should be systematic in your code layout so it will be easier for you to spot places where your code fails to follow the pattern.

proc sql ;
create table rr as 
select distinct
    AdmissionMonth
  , AgeGroup
  , County
  , Ethnicity
  , Race
  , HealthOutcomeID
  , MonthlyHosp
  , Sex
  , ADYEAR
  , count (case when ASTHMA_FLAG=1
from count_Asthma
group by AdmissionMonth
       , AgeGroup
       , County
       , Ethnicity
       , Race
       , HealthOutcomeID
       , MonthlyHosp
       , Sex
       , ADYEAR
; 
quit;

So the place where normal patterns breakdown is in your call to the COUNT() aggregate function.

count (case when ASTHMA_FLAG=1

You have the opening ( that indicates the start of the function arguments, but you never added any closing ).

You also have the start of a CASE clause, but you never finish it with an END keyword.

Do you want to count the number of times that variable has a value of 1?  You could finish the CASE like this so that COUNT has a value that is either populated (always 1 in the case) or missing.

count (case when ASTHMA_FLAG=1 then asthma_flag else . end)

But you might want to ADD instead of COUNT to get the number instead.

sum(case when ASTHMA_FLAG=1 then 1 else 0 end)

Which in SAS can be simplified to:

sum(ASTHMA_FLAG=1)

since SAS will evaluate such a boolean expression as either 1 (TRUE) or 0 (FALSE).

[fadamso12]

Thank you.

[fadamso12]

Hello, everyone. I am getting the error below when trying to count CO cases. How can I fix it? 

if CO_fire10 ~= 1 and CO_unknown_prelim1_10 = 1 then CO_unknown_prelim2_10 = 1;
		if CO_fire10 = 1 and CO_nonfire10 = 1 then CO_unknown_prelim3_10 = 1;
		if CO_unknown_prelim2_10 = 1 or CO_unknown_prelim3_10 = 1 then CO_unknown10 = 1;

		* sub-step f;
		if CO_fire10 = 1 then Cause = 1;*fire related ;
		if CO_nonfire10 = 1 then Cause = 2;*not fire related;
		if CO_unknown10 = 1 then Cause = 3;*Unknown mechanism or intent;
			if CO_fire10 = 1 and CO_nonfire10 = 1 then Cause = 3;
			if CO_fire10 = 1 and CO_unknown10 = 1 then Cause = 3;
			if CO_nonfire10 = 1 and CO_unknown10 = 1 then Cause = 3;
          HealthOutcomeID=3;
          MonthlyHosp=0;
			run;
proc sql ;
create table rr as
select distinct Admission_Month
  ,AgeGroup
  , County
  , Ethnicity
  , Race
  , HealthOutcomeID
  , MonthlyHosp
  , Sex
  , Admission_Year
  , count(case when (Cause=1) then sysid end) as IncidentCountFire,
		count(case when (Cause=2) then sysid end) as IncidentCountNonFire,
		count(case when (Cause=3) then sysid end) as IncidentCountUnknown
from  count_CO
group by Admission_Month
       , AgeGroup
       , County
       , Ethnicity
       , Race
       , HealthOutcomeID
	   , MonthlyHosp
       , Sex
       , Admission_Year
;
quit;

NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE expression.
ERROR: The following columns were not found in the contributing tables: sysid.

[PaigeMiller]

The error is about a variable named SYSID. The part of "no ELSE clause" is a NOTE, not an error.

Variable named SYSID does not exist. So you cannot use it.

As before, it would be EXTREMELY HELPFUL to both you and me if you explain, in words and not in SAS language or code, what you are trying to do here.

case when (Cause=1) then sysid end

[fadamso12]

Hello, sorry if I wasn’t clear. What I am trying to do is to count records by the cause. The variable “Cause” categorizes the records by fire-relatedness, and I am trying to count cases by that variable.  If you look at the first part of the code, I have categorized it so I want to know the number  CO poisoning which are fire-related or non-fire, or unknown.

[PaigeMiller]

Counting, in my opinion, is best done in PROC FREQ

proc freq data=countCO;
    tables cause/noprint out=_counts_;
run;

[Tom]

If you want the NOTE not to appear then just do what it asks.

count(case when (Cause=1) then sysid else null end) as IncidentCountFire

[fadamso12]

When I add else and null it, gives an error message saying: The following columns were not found in the contributing tables: null, sysid

[Tom]

---

@fadamso12 wrote:

When I add else and null it, gives an error message saying: The following columns were not found in the contributing tables: null, sysid

---

If SYSID is not in the dataset then why are trying the count how many times it was not missing?

Are you instead trying to count how many times your WHEN condition is met?  If so then why not just do that instead.

, sum((Cause=1)) as IncidentCountFire
, sum((Cause=2)) as IncidentCountNonFire
, sum((Cause=3)) as IncidentCountUnknown

If you have to use the SQL on some non-SAS database then you might have to use a CASE inside the SUM().

, sum(case when (Cause=1) then 1 else 0 end) as IncidentCountFire

And for some goofy databases you might even need do something more to prevent it from trying to make the sum() as a BOOLEAN to SMALLINT variable that will not be able to hold a large count because it might default to treating the constants 1 and 0 in the code as that type.

[fadamso12]

Hello @Tom 

The sum syntax works. The only issue is it is counting for cases = 1 and zeros are not included. 

[fadamso12]

It sounds like what you are saying about goof database maybe what is happening to me. Do you have any suggestion on how to fix it?