topic Re: Observed error while reading unstructured data (CSV) into Library in SAS Programming

Observed error while reading unstructured data (CSV) into Library

evach — Sat, 24 Apr 2021 13:32:48 GMT

Hi there,

I am using SAS Studio and trying to read unstructured data (CSV) into library.

Following are examples, where in example 1 run without errors, while in example 2 error observed is " Import cancelled. The dataset XL1.CLASS_BIRTHDATE1 is being used and cannot be replaced".

Can anyone help to understand the error, and for successful run of example 2.

EXAMPLE 1

(created library DATA1 and read SAS data tables from data folder. followed by using proc import to read CSV file in DATA1 library. Run was successful)

libname DATA1 "~/EPG1V2/data";

proc import datafile="~/EPG1V2/data/class_birthdate.csv" dbms=csv out=data1.class_birthdate1 REPLACE;
run;

EXMAPLE 2

(created library xl1 and read xl workbook class from data folder. followed by using proc import to read CSV file in xl1 library. observed error)

libname xl1 xlsx "~/EPG1V2/data/class.xlsx";

proc import datafile="~/EPG1V2/data/class_birthdate.csv" dbms=csv out=xl1.class_birthdate1 replace;
run;

Thanks!!

Re: Observed error while reading unstructured data (CSV) into Library

sbxkoenk — Sat, 24 Apr 2021 14:03:12 GMT

Hello,

Showing your log would learn us a lot more. (Next time, publish the log!).

But OK.

> "Import cancelled. The dataset XL1.CLASS_BIRTHDATE1 is being used and cannot be replaced".

I guess you are viewing an earlier version of XL1.CLASS_BIRTHDATE1 with a VT (ViewTable) or FSV (Full-Screen View) command. Is that possible? At least the table is in use "somewhere and somehow".

Solution: Close the table view or table usage and run your code again.

Cheers,

Koen

Re: Observed error while reading unstructured data (CSV) into Library

sbxkoenk — Sat, 24 Apr 2021 14:09:41 GMT

Oh, wait.

I see your libname statement now!

> libname xl1 xlsx "~/EPG1V2/data/class.xlsx";

Are you trying to create an Excel file (.xlsx) from a .csv file? You are not pointing to a folder but to a file.

You should do that in Excel (converting a .csv into a .xlsx).

And to export SAS tables to Excel you need a PROC EXPORT ((SAS/ACCESS Interface to PC Files External File Interface (EFI)).

Cheers,

Koen

Re: Observed error while reading unstructured data (CSV) into Library

Cynthia_sas — Sat, 24 Apr 2021 14:59:21 GMT

Hi:
I'm confused by what you're trying to do. In Example 1, you are IMPORTING (reading in) a CSV file and creating a SAS data set, data.class_birthdate1. In Example 2, you are IMPORTING (reading in) a CSV file and trying to create an XLSX workbook called CLASS.XLSX in the data subfolder.

The OUT= option for PROC IMPORT must specify a SAS dataset. You are trying to specify an Excel Workbook using the XLSX engine. When we make the data for the Programming 1 class, we give you an XLSX file called CLASS.XLSX. So it looks from your program as if you are trying to replace the original CLASS.XLSX file with the data in a CSV file by importing CLASS_BIRTHDATE.CSV.

The challenge with Example 2 is that for PROC IMPORT your OUT= must be a 1 level or 2 level SAS dataset name. My guess is that is why you are getting an import cancelled message.

Where are you working in the course? What Lesson, Activity or Practice are you doing? I do not recall a practice that tells you to import CLASS_BIRTHDATE.CSV and use the XLSX engine for the dataset created from PROC IMPORT. Your Example 1 is correct because you are reading in or importing a CSV file and writing out a SAS dataset using the regular Base LIBNAME engine to create a proprietary SAS data set table. Your Example 2 is confusing to me about what you're trying to import and why your output is not a SAS dataset.To import this CSV file, I would expect you to create a SAS dataset as output. like this:

We cover exporting to Excel in Lesson 6 in the class.

Cynthia

Re: Observed error while reading unstructured data (CSV) into Library

Tom — Sat, 24 Apr 2021 20:58:45 GMT

PROC IMPORT has a lot of quirks. Apparently one of them is that it cannot be used to write directly to a library defined with the XLSX engine.

So either create a work dataset and copy that.

libname xl1 xlsx "~/EPG1V2/data/class.xlsx";

proc import datafile="~/EPG1V2/data/class_birthdate.csv" dbms=csv out=class_birthdate1 replace;
run;
data xl1.class_birthdate1;
  set class_birthdate1 ;
run;

Or just skip the PROC IMPORT and read the text file directly with your own data step. If you do that you also eliminate all of the other quirky behavior of PROC IMPORT.

data xl1.class_birthdate1;
  infile "~/EPG1V2/data/class_birthdate.csv" dsd truncover firstobs=2;
  input .... ;
run;