Error when creating tables in PROC SQL ( ERROR 78-322: Expecting a '.'. )

[wolfpmd3]

Hello everyone,

I am working on a Macro which is almost ready. 

I am working from EG 7.15

The last component of it is counting the number of missing values for each variable and then dividing it by the number of observations to obtain the % if missing value for each Variable across n number of tables, and the it will output conditionally based on some other criteria after merging it to a another table.

The big issue that I don't seem to be able to get around is that it is giving me an error as shown in the following pictures:

 

What is curious to me is the fact that whenever I delete the create table statement  the Queries solve and produce the output shown below without any errors (there are 9 results, because there are 9 variables in this particular case). Of course this means that the syntax and everything else is right and the "error" must be somewhere in the create table statement.

 

Also, please note that the code is part of a loop that counts the number of variables from a list, then extracts the name of each variable from that list and repeats the process for all of them. 

Any ideas on why adding the "create table <name> as" statement generates this error?

Thank you all in advance.

Cheers,

Fernando

[novinosrin]

"Also, please note that the code is part of a loop that counts the number of variables from a list, then extracts the name of each variable from that list and repeats the process for all of them. "

Please post the whole code and the log for somebody to look into it and help

[wolfpmd3]

---

@novinosrin wrote:

Please post the whole code and the log for somebody to look into it and help

---

Here is the loop in which this code is embedde:

%DO j=1 %TO &num;

           %IF 0 EQ %sysevalf(%superq(lvar_ds&j)=, boolean) %THEN %DO;     

                %let nobs_ds&j=%scan(&nobs_list,%eval(&j+1));

                %let count_lvar_ds&j=%sysfunc(countw("&&lvar_ds&j."));

                %DO i=1 %TO &&count_lvar_ds&j.;

                     %let varmiss=%qscan(%quote(&&lvar_ds&j), &i);

                     %let nobs_ref=%scan(&nobs_list,1);

                     PROC SQL;

                           create table missing_&varmiss. as

                           select count(*) as Missing_count

                           from &ref.

                           where &varmiss is missing;

                     QUIT;

                %END;

           %END;

%END;

NOTE: when the text in RED is deleted there are no errors and PROC SQL produces the results shown above, only issue I cannot create the tables

[Amir]

Hi,

Try coding one example of what you think one of the fully resolved proc sql steps should look like and then try running it in isolation in SAS.

Are you sure you have write access to where you are trying to create the table? I know it looks like a work table, but just in case the user option has been set to other than work.

Regards,

Amir.

[wolfpmd3]

Yes, it is writing the tables to WORK.

I have split the code for that reason, all the macro variables solve to what they are supposed to. 

I am almost certain it has to do with the table name, but what?

Cheers,

Fernando

[ballardw · Accepted solution]

---

@wolfpmd3 wrote:

---

@novinosrin wrote:

Please post the whole code and the log for somebody to look into it and help

---

Here is the loop in which this code is embedde:

%DO j=1 %TO &num;

           %IF 0 EQ %sysevalf(%superq(lvar_ds&j)=, boolean) %THEN %DO;     

                %let nobs_ds&j=%scan(&nobs_list,%eval(&j+1));

                %let count_lvar_ds&j=%sysfunc(countw("&&lvar_ds&j."));

                %DO i=1 %TO &&count_lvar_ds&j.;

                     %let varmiss=%qscan(%quote(&&lvar_ds&j), &i);

                     %let nobs_ref=%scan(&nobs_list,1);

                     PROC SQL;

                           create table missing_&varmiss. as

                           select count(*) as Missing_count

                           from &ref.

                           where &varmiss is missing;

                     QUIT;

                %END;

           %END;

%END;

NOTE: when the text in RED is deleted there are no errors and PROC SQL produces the results shown above, only issue I cannot create the tables

---

It appears that VARMISS macro variable is supposed to be the name of a variable. Are you using %QUOTE in the following line of code because you have some name literal variables in your data set such as " name"n which characters that actually need quoting?

%let varmiss=%qscan(%quote(&&lvar_ds&j), &i);

I suspect that your VARMISS may have a leading blank in one or more variables as I get the same error with this code:

50   proc sql;
51      create table missing_ var as
                              ---
                              78
                              202
ERROR 78-322: Expecting a '.'.

ERROR 202-322: The option or parameter is not recognized and will be ignored.

52      select *
53      from sashelp.class
54      ;
55   quit;

Since there is something following the table that is not recognized as an SQL instruction it is assuming that you wanted a libname.dataset construct (not much variety really available with CREATE TABLE in that position).

[novinosrin]

@ballardw aesthetically pleasing to read your diligent post. Class act!

[wolfpmd3]

Thanks a lot!

I had completely overseen the fact that there was a leading blank in that when &varmiss solved. (Should have been using SYMBOLGEN)

---

@ballardw wrote:

---

---

It appears that VARMISS macro variable is supposed to be the name of a variable. Are you using %QUOTE in the following line of code because you have some name literal variables in your data set such as " name"n which characters that actually need quoting?

---

Answering your question: I had to use %quote because otherwise %qscan wouldn't work sinceit would have too many argument. (&&lvar_ds&j --> is a list of variables names that I need to create each of these tables for)

I just fixed it and worked perfectly fine --> Stripped leading and trailing blanks away and the issue is gone

Thanks a lot for your help!

Fernando

[SuryaKiran]

Hi,

I bet your not giving valid name for creating a table. Set OPTIONS SYMBOLGEN; and check the values for &varmiss. Leading space or some other invalid characters might be causing the issue.

Also, did you try PROC MEANS or PROC FREQ procedures to find missing values for variables. If not check here.

[wolfpmd3]

---

@SuryaKiran wrote:

Hi,

I bet your not giving valid name for creating a table. Set OPTIONS SYMBOLGEN; and check the values for &varmiss. Leading space or some other invalid characters might be causing the issue.

Also, did you try PROC MEANS or PROC FREQ procedures to find missing values for variables. If not check here.

---

I checked the post you mention, but since you need to create formats in order to be able to count missing values for char variables I decided to do it this way. Because this my method doesn't require any formatting and then extracting the info from the PROC FREQ output I decided to do it this way.

I will post the result later this week since counting calculating Missing percentage and counts seems a bit problematic (for char variables that is).

Yes, it was a leading blank that was causing this issue, I huge oversight on my end...

Thanks for the help

Best,

Fernando