I'm using EG 4.3 that resides on a server and using PROC IMPORT to read in an excel spreedsheet. Note that there is a space between the heading name 'Service Date'.
Here is my data in an excel spreadsheet:
ID | Service Date |
1 | 2/20/2012 |
2 | 2/20/2012 |
3 | 2/24/2012 |
4 | 2/28/2012 |
5 | 3/8/2012 |
6 | 3/6/2012 |
7 | 3/12/2012 |
8 | 3/22/2012 |
9 | 3/28/2012 |
10 | 3/29/2012 |
Here is my code to read in the above excel spreadsheet:
proc import out=P01.datetest
datafile="\\Dhvswdphapp01\user\USERS\RBeum\SAS\Input\Excelcs_Date_test.xls"
dbms=EXCELCS REPLACE;
sheet="Sheet1";
scantime=YES;
SERVER='rvwsascpt01';
PORT=9621;
run;
data P01.datetest_out;
set P01.datetest;
where Service_Date >= '01MAR2012'd;
run;
Here is the resulting error:
NOTE: The data set P01.DATETEST has 10 observations and 2 variables.
23
24
24 ! data P01.datetest_out;
25 set P01.datetest;
26 where Service_Date >= '01MAR2012'd;
ERROR: Variable Service_Date is not on file P01.DATETEST.
27 run;
NOTE: The SAS System stopped processing this step because of errors.
2 The SAS System 09:30 Tuesday, May 1, 2012
WARNING: The data set P01.DATETEST_OUT may be incomplete. When this step was
stopped there were 0 observations and 2 variables.
WARNING: Data set P01.DATETEST_OUT was not replaced because this step was
stopped.
NOTE: DATA statement used (Total process time):
real time 0.12 seconds
cpu time 0.00 seconds
It's my understanding that SAS replaces a SPACE with an underscore in column headings - is that correct?
Any ideas how to resolve with renaming the heading?
If you set OPTIONS VALIDVARNAME=V7, you will get underscores in your variable names instead of blanks.
EG keeps the space.
changing where Service_Date >= '01MAR2012'd;
to
where "Service Date"n >= '01MAR2012'd;
Or check to see if there are leading blanks and/or more than one blank between Service and Date.
Did you open P01.datetest to see what variable names they are ?
OR
use proc content check what variable name it truely contains .
Ksharp
If you set OPTIONS VALIDVARNAME=V7, you will get underscores in your variable names instead of blanks.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.