topic Re: Importing Data in SAS Programming

Importing Data

Ranjeeta — Fri, 27 Sep 2019 17:48:36 GMT

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;

Re: Importing Data

Kurt_Bremser — Fri, 27 Sep 2019 18:19:24 GMT

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.

Re: Importing Data

PGStats — Fri, 27 Sep 2019 21:36:48 GMT

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.

Re: Importing Data

Ranjeeta — Mon, 30 Sep 2019 13:34:32 GMT

Thankyou Will try