Variable is not on file

[programeerSAS]

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

[andreas_lds]

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.

[programeerSAS]

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

[Kurt_Bremser]

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.

[programeerSAS]

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

[programeerSAS]

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. 

[Kurt_Bremser]

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.

[programeerSAS]

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;

68

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

[Kurt_Bremser]

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.

[programeerSAS]

[Kurt_Bremser]

---

@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.

[programeerSAS]

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

[Kurt_Bremser · Accepted solution]

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.

[Patrick]

@Kurt_Bremser 

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

[FreelanceReinh]

---

@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.