topic Re: Trying to figure out why I am getting these errors in New SAS User

Trying to figure out why I am getting these errors

squeakums — Thu, 29 Sep 2022 18:24:23 GMT

Why am I getting these errors please?

ERROR 22-322: Syntax error, expecting one of the following: ;, ',', ANSIMISS, CROSS, EXCEPT, FULL, GROUP, HAVING, INNER, INTERSECT,
JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, UNION, WHERE.

ERROR 201-322: The option is not recognized and will be ignored.



Proc Sql;
Create Table A As
Select distinct
A.'ID'n,
B.'ITA ID'n,
B.'Start Date'n,
B.'Due Date'n,
B.'ITA Status'n,
B.'ITA Completion Date'n

From DETAILS092222 as A 
inner join ITA as B
on A.'ID'n = B.'ID'n
Order by 1 desc
;
Quit;

Re: Trying to figure out why I am getting these errors

Kurt_Bremser — Thu, 29 Sep 2022 18:29:04 GMT

Please do not post just the ERROR messages, post the complete log of the step(s) causing the ERRORs, including all code and messages.

Re: Trying to figure out why I am getting these errors

squeakums — Thu, 29 Sep 2022 18:32:34 GMT

I'm sorry that was my very first post on this site.

Does this suffice?

33         Proc Sql;
34         Create Table A As
35         Select distinct
36         A.'ID'n,
37         B.'ITA ID'n,
38         B.'Open Date'n,
39         B.'Due Date'n,
40         B.'ITA Status'n,
41         B.'ITA Completion Date'n
42         
43         From DETAILS092222 as A
                                      __
                                      22
2                                                          The SAS System                         12:28 Thursday, September 29, 2022

                                      201
ERROR 22-322: Syntax error, expecting one of the following: ;, ',', ANSIMISS, CROSS, EXCEPT, FULL, GROUP, HAVING, INNER, INTERSECT, 
              JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, UNION, WHERE.  

ERROR 201-322: The option is not recognized and will be ignored.

44         inner join ita as B
45         on A.'Id'n = B.'ID'n
46         Order by 1 desc
47         ;

Re: Trying to figure out why I am getting these errors

Kurt_Bremser — Thu, 29 Sep 2022 18:38:40 GMT

The ERROR pointer points to white space in the code, so I guess you have some invalid non-displayable character there. Remove all blank space which is not needed.

Re: Trying to figure out why I am getting these errors

squeakums — Thu, 29 Sep 2022 18:56:17 GMT

Okay, so I think the space issue was with one of the names.

So, I reran the code with the fix and now I'm getting this error

41         Order by 1 desc
42         ;
ERROR: File WORK.DETAILS.DATA does not exist.
ERROR: File WORK.IRA.DATA does not exist.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
43         Quit

Re: Trying to figure out why I am getting these errors

Kurt_Bremser — Thu, 29 Sep 2022 19:21:57 GMT

Which simply means that you try to use datasets which do not exist.

Single-level dataset names point to the non-permanent WORK library, which is deleted when a SAS session ends and newly created when a new SAS session starts. So you need to re-run the code which creates them when you start a new SAS session.

Re: Trying to figure out why I am getting these errors

SASKiwi — Thu, 29 Sep 2022 19:23:22 GMT

You need to have already created datasets DETAILS and IRA prior to your SQL query. Did you? In your earlier posted code they are named differently.

Re: Trying to figure out why I am getting these errors

squeakums — Thu, 29 Sep 2022 20:22:29 GMT

Yes, I imported them prior. I had changed the names because I didn't want to display the real names.

Re: Trying to figure out why I am getting these errors

squeakums — Fri, 30 Sep 2022 15:00:22 GMT

Just to make sure I am doing this right, before this code should I have written something create the set?

Re: Trying to figure out why I am getting these errors

ballardw — Fri, 30 Sep 2022 15:18:43 GMT

@squeakums wrote:

Yes, I imported them prior. I had changed the names because I didn't want to display the real names.

If your variable names contain information that you deem to sensitive to share then it is very likely that your data structure is wrong. Data belongs in values of variables, not in the names of the variables. Plus that sort of "structure" typically means that code gets hard to maintain as every time you add a new "value" into the variable names that all of the code using that set has to be updated to account for that.

A brief example

Suppose you have a data set that has City names to indicate the place where some value, such as home sale prices are stored, i.e. it looks like:

Date Boston Chicago Denver Dallas

When you get new values any code used to do things like determine the average price of homes across those cities needs to include a new variable for EACH city.

If however you have your data as

Date CityName HomePrice

you get two things quickly: To get the average of homeprices you just use the one variable HomePrice. Second less obvious is that when you combine data from different periods you don't run into issues.

Less obvious to new SAS users, you can get summaries at the CityName level by using BY group processing or as a Class variable.

Another is the name of the HomePrice variable is easy to follow as to what it measures or could be used for. Not obvious with values under Boston.

Another consideration is if you have a data set with information like Average Household income if your data is the same

Date Boston Chicago Denver Dallas

with the values for the City the Income then combining the two bits to do something like Income / Home price analysis is going to be very

complicated. If the data however is

Date CityName Income

then it is easy (barring spelling problems) to combine data on the Date CityName and get a set

Date CityName HomePrice Income

and do analysis using the values.

Note the CityName does not "reveal" the areas you may be interested in like variable names of Boston would.

Re: Trying to figure out why I am getting these errors

squeakums — Fri, 30 Sep 2022 17:52:25 GMT

I have this figured out now -

I should have updated to

From FolderName.ITA as A
inner join FolderName.ID as B
on A.'Service ID'n = B.'Service ID'n

ERROR 22-322: Syntax error, expecting one of the following: ;, ',', ANSIMISS, CROSS, EXCEPT, FULL, GROUP, HAVING, INNER, INTERSECT, 
              JOIN, LEFT, NATURAL, NOMISS, ORDER, OUTER, RIGHT, UNION, WHERE.  

ERROR 201-322: The option is not recognized and will be ignored.

44         inner join ita as B
45         on A.'Id'n = B.'ID'n
46         Order by 1 desc
47         ;

Re: Trying to figure out why I am getting these errors

Kurt_Bremser — Fri, 30 Sep 2022 20:45:31 GMT

The complete log, please. Messages without the code causing them are useless.