topic Re: Import CSV issue in SAS Programming

Import CSV issue

Q1983 — Tue, 16 Aug 2022 19:31:53 GMT

I have a csv file within a share drive with an &

%LET MTG = \\p1l-nas-02.dlnet.com\mort\SS&A Folder\mortgage

When I run the code it says WARNING: Apparent symbolic reference A not resolved

I renamed this way

%LET MTG = \\p1l-nas-02.dlnet.com\mort\SS(&)A Folder\mortgage

I do not get the error when I add (&) however when I run the following code

proc import 
datafile="&MTG.\REPORT.CSV"
out=REPORT  
dbms=EXCELCS replace; /*EXCELCS*/
server="sas-pcff.mortg-prd.com"; 
port=9922 ;
SERVERUSER="&suserid."; SERVERPASS="&mypass.";
run;

I get the error It does not exist or it is already opened exclusively by another user, or you need permission
I am thinking that I am not actually reading in the sharedrive. I do not know why the user name the sharedrive with an '&' however is there a fix so I can read in the file??

Re: Import CSV issue

Tom — Tue, 16 Aug 2022 19:42:01 GMT

Try adding macro quoting.

For your original path you could try just adding it when defining it.

%LET MTG = \\p1l-nas-02.dlnet.com\mort\SS%str(&)A Folder\mortgage;

For the second try adding it in the call to PROC IMPORT.

datafile="%superq(MTG)\REPORT.CSV"

Re: Import CSV issue

Kurt_Bremser — Tue, 16 Aug 2022 19:57:46 GMT

Please post the complete log of your step by copy/pasting it into a window opened with this button:

Re: Import CSV issue

Kurt_Bremser — Tue, 16 Aug 2022 20:06:17 GMT

And why are you using the EXCELCS engine for a text file? EXCELCS is for .xls files.

Re: Import CSV issue

Q1983 — Tue, 16 Aug 2022 21:05:20 GMT

I used Excel because when I used CSV i received an error. Is that the correct reference in this case

Re: Import CSV issue

ballardw — Tue, 16 Aug 2022 21:21:48 GMT

@Q1983 wrote:
I used Excel because when I used CSV i received an error. Is that the correct reference in this case

Show the code that you use with CSV with the error(s)

You may have to open the file, not with spreadsheet software, with a plain next editor like Notepad to see the actual contents to set the proper delimiter and options.

Re: Import CSV issue

Tom — Tue, 16 Aug 2022 22:35:07 GMT

I do not see anything in the documentation that says you can use the SAS PC Files Server to access CSV files. Just Excel or Access files. if the file is actually a CSV file then move it to a location that SAS can read directly. Or use Excel to read the file and save it as an XLSX file and then try using PC Files Server to read the XLSX file.

Re: Import CSV issue

Kurt_Bremser — Wed, 17 Aug 2022 06:24:55 GMT

One does not use PROC IMPORT for CSV files. Write the data step yourself, according to the file documentation, for consistent and controlled results. Or use PROC IMPORT only once, and copy the data step code from the log and correct/optimize it.

The PC Files server is only for Excel workbooks and MS Access (.mdb) files, and is only needed when you run SAS on a non-Windows platform where the Microsoft-supplied module for reading older MS Office files is not available. With the introduction of .xlsx and the (now internal to SAS) XLSX engine SAS can read such files on all supported platforms.

You have to store the file on the SAS server (upload facility in SAS Studio, or Copy Files task in Enterprise Guide) or on a shared network location which is also accessible (mounted) on the SAS server.