topic Re: Proc SQL syntax errors in SAS Studio

Proc SQL syntax errors

oadeyemi — Thu, 22 Aug 2019 22:45:25 GMT

Good day everyone.

I am trying to run proc sql command and I am facing some difficulties

Background:

fatal represent numbers per case

countycode represent counties for each fatal.

I intend to generate a table that has unique countycodes and average fatal counts per county.

Below is my code

proc sql;
select fatals, countycode
	from county 
	group by countycode
	mean (fatals) as fatalcount;
quit;

I keep getting syntax errors.

I will appreciate help with this.

Thank you

Re: Proc SQL syntax errors

PaigeMiller — Thu, 22 Aug 2019 22:50:53 GMT

proc sql;
select fatals, countycode, mean (fatals) as fatalcount
	from county 
	group by countycode;
quit;

Re: Proc SQL syntax errors

Reeza — Thu, 22 Aug 2019 22:58:28 GMT

FYI - the majority of SQL is the same across all languages and most tutorials will work with SAS as well.

Here's the w3 tutorials:

https://www.w3schools.com/sql/default.asp

And the SQL library in the documentation has many good examples:

https://support.sas.com/documentation/cdl//en/sqlproc/69822/HTML/default/viewer.htm#p016wt256y0c2on1dvsm0a7ktvva.htm

See the side bar for other common usage cases.

Re: Proc SQL syntax errors

novinosrin — Thu, 22 Aug 2019 23:02:03 GMT

Sir @PaigeMiller

Even the corrected code

proc sql;
select fatals, countycode, mean (fatals) as fatalcount
	from county 
	group by countycode;
quit;

will cause SQL processor the computed mean here fatalcount merge back(remerge) with the original. Of course we are not aware of what OP's exact WANT but methinks we can make a guess from "I intend to generate a table that has unique countycodes ".

Therefore excluding fatals from the SELECT should get the desired result.

proc sql;
select  countycode, mean (fatals) as fatalcount
	from county 
	group by countycode;
quit;

Re: Proc SQL syntax errors

oadeyemi — Thu, 22 Aug 2019 23:39:02 GMT

Thank you.

I understand why I keep seeing the syntax error.

Although I did not get a unique ID though.

Thank you

Re: Proc SQL syntax errors

oadeyemi — Thu, 22 Aug 2019 23:40:25 GMT

Thank you.

This worked.

And I generated unique ID.

Thank you

Re: Proc SQL syntax errors

oadeyemi — Thu, 22 Aug 2019 23:46:17 GMT

Thank you for the link.

Very helpful.

Thanks for the information.