Proc Import Issue

[J_T1]

Hi all,

Long time lurker first time poster. Apologies if formatting/explanation is patchy.

I've recently changed companies and have come across an issue I haven't experienced before.

I'm trying to automate some basic reporting and I'm having trouble importing a CSV file. I can do it through the wizard just fine, however the csv extract changes daily (date/time stamp) so I want to complete this using a proc import macro.

The problem seems to be that our SAS server is on UNIX and my PC is windows.

My code below.

PROC IMPORT OUT= WORK.IMPW DATAFILE= "\\myDrive\DataShare\Brio\Job\myFile_1-20171214.csv..csv"

DBMS=csv REPLACE;

SHEET="Data";

GETNAMES=YES;

RUN;

And this is what I get from the log

15 DBMS=csv REPLACE;

NOTE: The previous statement has been deleted.

16 SHEET="Data";

_____

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

17 GETNAMES=YES;

18 RUN;

ERROR: Physical file does not exist,

/saslev1comp/software/config/Lev1/SASEG/\\myDrive\DataShare\Brio\Job\myFile_1-20171214.csv.

It looks like I'm limited to only using proc import for files in our sas drive (saslec1comp) If I place the csv somewhere in that folder the proc  import works fine.

My IT departments solution was to just use the wizard, hoping someone else has come across this hurdle before.

Thanks

J_T1

[Tom]

Enterprise Guide runs on PCs.  It looks from your error message that you are running SAS on Unix.

Either map the network share to a Unix directory.  So if you network folder looks like this:

\\myDrive\DataShare

Then get your support team to mount that share at some location on the Unix machine(s) where SAS is running.  Depending on how your site mounts disks perhaps to something like

/media/myDrive/DataShare

Then in your SAS code you would (assuming the extra '..csv' at the end was a mistake) refer the file as :

/media/myDrive/DataShare/Brio/Job/myFile_1-20171214.csv

If you cannot get the drive mounted then you will need to use the File Upload task in EG to upload the file and then run PROC IMPORT on the uploaded file.

Remember that on Unix file names are case sensitive.  So Brio and brio would refer to different files/directories.

[ballardw]

---

@J_T1 wrote:

Hi all,

Long time lurker first time poster. Apologies if formatting/explanation is patchy.

I've recently changed companies and have come across an issue I haven't experienced before.

I'm trying to automate some basic reporting and I'm having trouble importing a CSV file. I can do it through the wizard just fine, however the csv extract changes daily (date/time stamp) so I want to complete this using a proc import macro.

The problem seems to be that our SAS server is on UNIX and my PC is windows.

My code below.

15 DBMS=csv REPLACE;

NOTE: The previous statement has been deleted.

16 SHEET="Data";

_____

180

ERROR 180-322: Statement is not valid or it is used out of proper order.

17 GETNAMES=YES;

J_T1

---

CSV files does not have sheets. So that is the cause of the highlighted error.

If the file changes only in content then you are doing yourself a disservice in using proc import to read daily files. Unless your date is remarkably consistent you have an extreme likelihood of data variables changing types and character variables changing lengths which can lead to serious problems is you ever combine data. Or compare results and find that some names have been truncated in one set.

Run proc import once, the wizard would be fine. The proc import will generate data step code that you can copy from the log and save in the editor. Check that the lengths, types in informats match your specifications for the file (you do have a specification document don't you?). If not then modify the code to match as needed. If the automatically generated variable names aren't nice, change them. Assign meaningful labels. When all of  that is working then save the code.

then All you need to do is point the INFILE to the new file name and change the name of the output data set (if desired).

BTW when using proc import with DBMS files that support the option such as CSV use GUESSINGROWS=max; so that you have the best chance of the resulting data step matching what you want. Without that option then SAS only looks at the first 20 rows to check variable type and length. If you have a numeric variable that is only occasionally populated it may be treated as character if not appearing in the first rows. Or if a field like Companyname has IBM for the first 30 rows then "General Electric" further in the data may be "Gen".