Hello I would appreciate some advice
Im importing an Excel tab and what would the $ sign at th end of the sheet name do to the proc import step?
Also
If i import a sheet and i see some blank rows at the end of the dataset ie after the last row why would that occur?
Also there are columns with F7 F8 added after the last column
Would appreciate some advice as to how I can correct those errors
Regards
PROC IMPORT OUT=Site_number
DATAFILE= "Y:\KNOWLEDGE MANAGEMENT\Analytics\EVT PMM\Common Resources\EVT Reference.xlsx"
DBMS=EXCEL REPLACE;
RANGE="SiteNumber1718$";
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
You are not importing a sheet, but a named range. That range seems to expand beyond the used (filled with data) cells.
PS a sheet is automatically also a range:
SHEET="XXX" is equivalent to
RANGE="XXX$"
So it now seems to me that you address the whole sheet as a range, and that, at some time, more cells were "used" in that sheet (eg simply by applying a format or just entering a blank) than actually have data in them. Delete the columns and rows in your sheet that you do not want imported, before running the proc import. Or use a range with cell addressing.
Try
PROC IMPORT OUT=Site_number(where=(myVar is not missing))
DATAFILE= "Y:\KNOWLEDGE MANAGEMENT\Analytics\EVT PMM\Common Resources\EVT Reference.xlsx"
DBMS=EXCEL REPLACE;
RANGE="SiteNumber1718$A1:F99999";
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
where myVar is a variable that is never missing.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.