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
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.
@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".
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: