topic Re: Why the variables can't be found in the table? in SAS Studio

Why the variables can't be found in the table?

Eilot — Fri, 16 Nov 2018 06:22:12 GMT

Hey, I always used data step to read in data, but this time I used the "proc import" to import the data, and the dataset is imported successfully.

But when I tried to use the variables in "proc sql", I selected the variables by their names showed in the table, but SAS always reported that "The following columns were not found in the contributing tables: Category".

I want to know if I did something wrong, because when I used the "proc contents" or "describe table" in sql, the variable names are exactly what I used, and I also tried renaming the variables and changing the option validvarname to any, but they didn't work.

proc import datafile="/location" 
	out=work.jeopardy dbms=csv replace; 
	getnames=yes; 
run;

data jeopardy1;
 set WORK.jeopardy;
 rename 'Show Number'n = ShowNumber;
 rename 'Air Date'n = AirDate;
run;

proc contents data = jeopardy1;
run;

proc sql outobs= 10;
	select Category
	from jeopardy1;
quit;

I appreciate it if anyone can help me with this.

Re: Why the variables can't be found in the table?

PaigeMiller — Thu, 15 Nov 2018 13:25:35 GMT

but SAS always reported that "The following columns were not found in the contributing tables: Category".

This means that Category is not in your data set. Despite the fact that you think it is in your data set, it is NOT. When SAS and a user disagree, I believe SAS.

So, you might want to show us the SASLOG (click on the {i} icon and paste it in there), and also show us (a portion of) the data.

Re: Why the variables can't be found in the table?

ballardw — Thu, 15 Nov 2018 16:12:47 GMT

It would likely be very informative to show the results of PROC CONTENTS on WORK.JEOPARDY as the result of Proc Import than on the modified data set.

Another thing is to check the log after Proc Import with a delimited file. You will find a data step generated by the procedure which will show the variable INFORMAT, FORMAT information for the variables created.

It is very hard to say what might be going on related to a message such as

The following columns were not found in the contributing tables: Category".

since none of your code shown actually uses Category

Re: Why the variables can't be found in the table?

Eilot — Fri, 16 Nov 2018 06:30:56 GMT

Hey, I'm sorry, the Category variable should be in the proc sql step(I typed Value), I've changed it. But no matter what variable I used in my dataset(Value or Category or others ), it always report this error.

And I found the INFORMAT/FORMAT information, I pasted it in my post.

Re: Why the variables can't be found in the table?

Eilot — Fri, 16 Nov 2018 06:43:13 GMT

Here is the log after I imported the data.

 1          OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 72         
 73         proc import datafile="location"
 74         out=work.jeopardy dbms=csv replace;
 75         getnames=yes;
 76         run;
 
 NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to 
 WORK.PARMS.PARMS.SLIST.
 77          /**********************************************************************
 78          *   PRODUCT:   SAS
 79          *   VERSION:   9.4
 80          *   CREATOR:   External File Interface
 81          *   DATE:      16NOV18
 82          *   DESC:      Generated SAS Datastep Code
 83          *   TEMPLATE SOURCE:  (None Specified.)
 84          ***********************************************************************/
 85             data WORK.JEOPARDY    ;
 86             %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
 87             infile 'location' delimiter = ',' MISSOVER DSD
 87       ! lrecl=32767 firstobs=2 ;
 88                informat "Show Number"N best32. ;
 89                informat " Air Date"N yymmdd10. ;
 90                informat " Round"N $9. ;
 91                informat " Category"N $33. ;
 92                informat " Value"N $6. ;
 93                informat " Question"N $122. ;
 94                informat " Answer"N $23. ;
 95                format "Show Number"N best12. ;
 96                format " Air Date"N yymmdd10. ;
 97                format " Round"N $9. ;
 98                format " Category"N $33. ;
 99                format " Value"N $6. ;
 100               format " Question"N $122. ;
 101               format " Answer"N $23. ;
 102            input
 103                        "Show Number"N
 104                        " Air Date"N
 105                        " Round"N  $
 106                        " Category"N  $
 107                        " Value"N  $
 108                        " Question"N  $
 109                        " Answer"N  $
 110            ;
 111            if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
 112            run;

 
 NOTE: 216930 records were read from the infile 'location'.
       The minimum record length was 55.
       The maximum record length was 969.
 NOTE: The data set WORK.JEOPARDY has 216930 observations and 7 variables.
 NOTE: DATA statement used (Total process time):
       real time           0.23 seconds
       user cpu time       0.21 seconds
       system cpu time     0.03 seconds
       memory              10549.37k
       OS Memory           95316.00k
       Timestamp           11/16/2018 06:35:29 AM
       Step Count                        83  Switch Count  2
       Page Faults                       0
       Page Reclaims                     294
       Page Swaps                        0
       Voluntary Context Switches        12
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           91656
       
 
 216930 rows created in WORK.JEOPARDY from jeopardy.csv.
   
   
   
 NOTE: WORK.JEOPARDY data set was successfully created.
 NOTE: The data set WORK.JEOPARDY has 216930 observations and 7 variables.
 NOTE: PROCEDURE IMPORT used (Total process time):
       real time           0.36 seconds
       user cpu time       0.31 seconds
       system cpu time     0.06 seconds
       memory              10549.37k
       OS Memory           95832.00k
       Timestamp           11/16/2018 06:35:29 AM
       Step Count                        83  Switch Count  10
       Page Faults                       0
       Page Reclaims                     5565
       Page Swaps                        0
       Voluntary Context Switches        84
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           91704
       
 
 113        
 114        
 115        
 116        
 117        proc sql outobs= 10;
 118        select Category
 119        from work.jeopardy;
 ERROR: The following columns were not found in the contributing tables: Category.
 NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
 120        quit;
 NOTE: The SAS System stopped processing this step because of errors.
 NOTE: PROCEDURE SQL used (Total process time):
       real time           0.00 seconds
       user cpu time       0.00 seconds
       system cpu time     0.00 seconds
       memory              6908.46k
       OS Memory           94948.00k
       Timestamp           11/16/2018 06:35:29 AM
       Step Count                        84  Switch Count  0
       Page Faults                       0
       Page Reclaims                     242
       Page Swaps                        0
       Voluntary Context Switches        0
       Involuntary Context Switches      0
       Block Input Operations            0
       Block Output Operations           0
       
 121        
 122        OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
 135

And the picture is part of my dataset.

Re: Why the variables can't be found in the table?

PaigeMiller — Fri, 16 Nov 2018 11:17:04 GMT

Here is the key part of your SASLOG

 102            input
 103                        "Show Number"N
 104                        " Air Date"N
 105                        " Round"N  $
 106                        " Category"N  $

See that? The variable is not named CATEGORY. The variable name has space before the letter C of CATEGORY. In PROC SQL (and everywhere else in SAS), you have to refer to the variable as " CATEGORY"N. You might want to rename these variables, so that the spaces are no longer present, but that's up to you.

Re: Why the variables can't be found in the table?

Eilot — Fri, 16 Nov 2018 16:02:32 GMT

Thank you 🙏!