topic Re: Variable is not on file in SAS Enterprise Guide

Variable is not on file

programeerSAS — Wed, 09 Mar 2022 08:08:14 GMT

Hi there- I am running this "simple" piece of code to isolate data based on a date variable.

data electric.elec_jan;

set electric.elec;

where DATE < '31Jan2022'd;

run:

However, the above gives this error even though "DATE" IS in the dataset?

ERROR: Variable DATE is not on file ELECTRIC.ELEC

Please help

Re: Variable is not on file

andreas_lds — Wed, 09 Mar 2022 06:38:54 GMT

Please post the code of proc contents also.

In the set statement you are using electric.elec, but the error message complains about Date not being in ELECTRIC.SBSA_ELEC.

Re: Variable is not on file

Kurt_Bremser — Wed, 09 Mar 2022 06:45:52 GMT

The log is not from the code you posted, as ELECTRIC.SBSA_ELEC is not used there.

When posting logs, ALWAYS include the complete code, and use this button to post it:

The "little running man" right next to it is for SAS code.

Re: Variable is not on file

programeerSAS — Wed, 09 Mar 2022 07:46:09 GMT

Hi Kurt- I am using SAS studio via browser, no such option

Re: Variable is not on file

programeerSAS — Wed, 09 Mar 2022 07:46:53 GMT

Hi Andreas - the proc contents snippet showing the variable that is causing the problem is in the original post

Re: Variable is not on file

programeerSAS — Wed, 09 Mar 2022 08:57:42 GMT

I tried this code as well

 DATE2 = INPUT(PUT(DATE,8.),YYMMDD10.);
  FORMAT DATE2 YYMMDD8.;

But all that does is create another DATE and DATE2 field- both empty and no errors in the log.

It is as if I am unable to reference the DATE field of the original dataset.

This code also did not work

my_date_char = put(DATE, 8.);

It creates anothe DATE filed as opposed to converting the existing DATE field to a character value.

Re: Variable is not on file

Kurt_Bremser — Wed, 09 Mar 2022 09:19:06 GMT

@programeerSAS wrote:
Hi Kurt- I am using SAS studio via browser, no such option

These buttons are part of the SAS Community interface, not SAS Studio. In SAS Studio, switch to the Log tab, mark and copy the log text, then paste it into the appropriate window here as described.

Re: Variable is not on file

Kurt_Bremser — Wed, 09 Mar 2022 09:31:14 GMT

Start at the beginning of your code. Inspect the first dataset which you use; take note of the type and other attributes there.

Then, once you have verified that date is a numeric variable with a date format attached and does have non-missing values (e.g. run PROC FREQ on the dataset), proceed down the path of your process; see if you have any NOTEs beyond those recording execution time and the number of observations and variables in newly created datasets.

If anything beyond this happens and puzzles you, post the log as described.

A "good" step has nothing in the log beyond the code, the execution time (and other execution stats), the numbers of observations in incoming and outgoing datasets, and the number of variables in a new dataset. ANY further NOTE points to a possible issue and must be dealt with.

The more details you provide, the easier it is for us to help you; therefore, always post complete codes and complete logs, so any message in the log is in the right context.

Re: Variable is not on file

programeerSAS — Wed, 09 Mar 2022 09:51:31 GMT

This is how I read in the data

proc import datafile= "/home/u44662193/Electricity/CMP5.FULLEXT.CSV"
dbms=dlm
out=electric.Elec
replace;
GETNAMES=YES;

delimiter=',';
guessingrows=10000;

run;

SAS assigned "DATE" as the first field correctly, and it is informant yymmdd10.

The CONTENTS Procedure

Data Set NameObservationsMember TypeVariablesEngineIndexesCreatedObservation LengthLast ModifiedDeleted ObservationsProtectionCompressedData Set TypeSortedLabel Data Representation Encoding

ELECTRIC.ELEC	2862498
DATA	8
V9	0
2022/03/09 11:46:22	80
2022/03/09 11:46:22	0
	NO
	NO

SOLARIS_X86_64, LINUX_X86_64, ALPHA_TRU64, LINUX_IA64
utf-8 Unicode (UTF-8)

Engine/Host Dependent InformationData Set Page SizeNumber of Data Set PagesFirst Data PageMax Obs per PageObs in First Data PageNumber of Data Set RepairsFilenameRelease CreatedHost CreatedInode NumberAccess PermissionOwner NameFile SizeFile Size (bytes)

131072
1751
1
1635
1599
0
/home/u44662193/elec.sas7bdat
9.0401M6
Linux
199361150
rw-r--r--
u44662193
219MB
229638144

Alphabetic List of Variables and Attributes# Variable Type Len Format Informat13578462

DATE	Num	8	YYMMDD10.	YYMMDD10.
ACC NUM	Num	8	BEST12.	BEST32.
AMOUNT	Num	8	BEST12.	BEST32.
DESCR	Char	20	$20.	$20.
NARR	Char	15	$15.	$15.
SER NO	Char	1	$1.	$1.
STATEMENT CODE	Num	8	BEST12.	BEST32.
TC	Num	8	BEST12.	BEST32.

Running your proc freq to check the DATE field it says this:

proc freq data = electric.Elec;
tables Date;
run;

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

69 proc freq data = electric.Elec;

70 tables Date;

ERROR: Variable DATE not found.

Which brings me back to the previous reply, it is as if I am unable to do ANYTHING with this DATE field that SAS created for me as part of the import.

#stumped

Re: Variable is not on file

Kurt_Bremser — Wed, 09 Mar 2022 10:02:13 GMT

Please open your csv file with a text editor (not with Excel!) and copy/paste the first few lines into the code box (</> button).

Also, copy the import data step (which PROC IMPORT creates) from the log and post it into a code box.

Re: Variable is not on file

programeerSAS — Wed, 09 Mar 2022 10:07:21 GMT

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
68
69 proc import datafile= "/home/u44662193/Electricity/CMP5.FULLEXT.CSV"
70 dbms=dlm
71 out=electric.Elec
72 replace;
73 GETNAMES=YES;
74
75 delimiter=',';
76 guessingrows=10000;
77
78 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.
79 /**********************************************************************
80 * PRODUCT: SAS
81 * VERSION: 9.4
82 * CREATOR: External File Interface
83 * DATE: 09MAR22
84 * DESC: Generated SAS Datastep Code
85 * TEMPLATE SOURCE: (None Specified.)
86 ***********************************************************************/
87 data ELECTRIC.ELEC ;
88 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
89 infile '/home/u44662193/Electricity/CMP5.FULLEXT.CSV' delimiter = ',' MISSOVER DSD lrecl=32767 firstobs=2 ;
90 informat " DATE"N yymmdd10. ;
91 informat TC best32. ;
92 informat "ACC NUM"N best32. ;
93 informat "SER NO"N $1. ;
94 informat AMOUNT best32. ;
95 informat "STATEMENT CODE"N best32. ;
96 informat DESCR $20. ;
97 informat NARR $15. ;
98 format " DATE"N yymmdd10. ;
99 format TC best12. ;
100 format "ACC NUM"N best12. ;
101 format "SER NO"N $1. ;
102 format AMOUNT best12. ;
103 format "STATEMENT CODE"N best12. ;
104 format DESCR $20. ;
105 format NARR $15. ;
106 input
107 " DATE"N
108 TC
109 "ACC NUM"N
110 "SER NO"N $
111 AMOUNT
112 "STATEMENT CODE"N
113 DESCR $
114 NARR $
115 ;
116 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
117 run;

NOTE: The infile '/home/u44662193/Electricity/CMP5.FULLEXT.CSV' is:
Filename=/home/u44662193/Electricity/CMP5.FULLEXT.CSV,
Owner Name=u44662193,Group Name=oda,
Access Permission=-rw-r--r--,
Last Modified=03 March 2022 21:47:02,
File Size (bytes)=286296808

NOTE: 2862498 records were read from the infile '/home/u44662193/Electricity/CMP5.FULLEXT.CSV'.
The minimum record length was 98.
The maximum record length was 112.
NOTE: The data set ELECTRIC.ELEC has 2862498 observations and 8 variables.
NOTE: DATA statement used (Total process time):
real time 3.76 seconds
user cpu time 2.37 seconds
system cpu time 0.26 seconds
memory 11772.56k
OS Memory 36636.00k
Timestamp 2022/03/09 10:05:08 AM
Step Count 30 Switch Count 3
Page Faults 0
Page Reclaims 394
Page Swaps 0
Voluntary Context Switches 4061
Involuntary Context Switches 4
Block Input Operations 0
Block Output Operations 448272

2862498 rows created in ELECTRIC.ELEC from /home/u44662193/Electricity/CMP5.FULLEXT.CSV.

NOTE: ELECTRIC.ELEC data set was successfully created.
NOTE: The data set ELECTRIC.ELEC has 2862498 observations and 8 variables.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 7.23 seconds
user cpu time 5.66 seconds
system cpu time 0.35 seconds
memory 11772.56k
OS Memory 37152.00k
Timestamp 2022/03/09 10:05:08 AM
Step Count 30 Switch Count 11
Page Faults 0
Page Reclaims 7781
Page Swaps 0
Voluntary Context Switches 4209
Involuntary Context Switches 9
Block Input Operations 288
Block Output Operations 448360

118
119 proc freq data = electric.Elec;
120 tables DATE;
ERROR: Variable DATE not found.
121 run;

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE FREQ used (Total process time):
real time 0.00 seconds
user cpu time 0.00 seconds
system cpu time 0.00 seconds
memory 1787.68k
OS Memory 30888.00k
Timestamp 2022/03/09 10:05:08 AM
Step Count 31 Switch Count 0
Page Faults 0
Page Reclaims 253
Page Swaps 0
Voluntary Context Switches 1
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0

122
123 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;
133

Re: Variable is not on file

programeerSAS — Wed, 09 Mar 2022 10:08:57 GMT

Re: Variable is not on file

Kurt_Bremser — Wed, 09 Mar 2022 10:42:26 GMT

See this VERY important log line:

informat " DATE"N yymmdd10. ;

Your CSV file obviously contains blank space in the header line, so PROC IMPORT dutifully uses a name literal (quotes and trailing N) to keep this in the variable name, which will now force you to do the same throughout your code, which is NOT GOOD AT ALL (unless you crave unnecessary extra work).

Submit

options validvarname=v7;

before running proc import, or (best option by far) copy the data step code from the log, fix it, and run that directly. Replace the blanks in variable names with underlines, so you can use the variables without name literals.

Re: Variable is not on file

Patrick — Wed, 09 Mar 2022 11:08:52 GMT

@Kurt_Bremser

Kudos - that was certainly a piece of masterfully guiding the OP to provide the info you needed to identify the issue.

Re: Variable is not on file

FreelanceReinh — Wed, 09 Mar 2022 12:00:32 GMT

@Patrick wrote:

@Kurt_Bremser

Kudos - that was certainly a piece of masterfully guiding the OP to provide the info you needed to identify the issue.

I had the same thought. Thanks, @Patrick, for expressing it.

This thread is also yet another example where complete listing output, posted into a code box, would have clarified things very early:

Alphabetic List of Variables and Attributes

#    Variable    Type    Len

1     DATE       Num       8
2    ACC NUM     Num       8
...

Both the indentation of "DATE" and the seemingly non-alphabetic sort order -- neither of which could be seen in the partial HTML output shown in @programeerSAS's initial post -- would have revealed the cause of the issue.

Re: Variable is not on file

programeerSAS — Wed, 09 Mar 2022 11:56:38 GMT

Hi Kurt-

I use to code a lot in SAS but the import step had this one little problem on the raw data side that you identified spot-on!
I have never seen or used the

options validvarname=v7;

but now I know it!
Thank you sincerely for repeatedly looking at my problem and solving it! You are a machine!

Re: Variable is not on file

ballardw — Wed, 09 Mar 2022 15:21:37 GMT

@programeerSAS wrote:

Hi Kurt-

I use to code a lot in SAS but the import step had this one little problem on the raw data side that you identified spot-on!
I have never seen or used the
options validvarname=v7;
but now I know it!
Thank you sincerely for repeatedly looking at my problem and solving it! You are a machine!

It is quite possible that you hadn't needed the option before. The validvarname option was established to handle, mostly, variables in external databases that allowed different variable naming conventions. Then some of the SAS interfaces lately have defaulted to the Validvarname=any which allows these other naming conventions. You may also run into issues with VALIDMEMNAME=Extend which allows other characters for data set and other file names though won't create member names with leading spaces.The option VALIDMEMNAME=compatible sets the "normal" SAS data set name rules.