I am working on SAS 9.4 which is available on cloud Unix environment and I have a file which is available in the window environment. I know I can import this file manually going file-->import data. So according to me if this is available manually then there should be any such way by coding as well. I need to automate it so I can't go every time and import this manually.
Though I have my File in MS-Access, for ease purpose I mentioned csv also. If you can solve the problem for either CSV file or access file. It will work for me.
You need to license SAS/ACCESS to PC Files to have the ability to read/write Microsoft Access files:
SAS/ACCESS® 9.4 Interface to PC Files: Reference, Fourth Edition
http://go.documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.3&docsetId=acpcref&docsetTarget=ti...
If you want to read/write files from your UNIX environment, then you must license the software for that environment.
You also need the SAS PC Files Server running on a Windows-based machine:
Downloads: SAS PC Files Server
https://support.sas.com/downloads/package.htm?pid=2167
The UNIX and Windows machines must be able to communicate with one another.
Here are the steps to import the file in your UNIX environment (similar steps for Windows):
1. Place the Microsoft Access database file in C:\temp\ on the Windows machine. In this example I use the DEMO.ACCDB file from the SAS installation located at C:\Program Files\SASHome\SASFoundation\9.4\access\sasmisc\demo.accdb.
2. Start the SAS PC Files Server on the Windows machine.
3. Click File > Import Data on the UNIX machine to open the SAS Import Wizard.
4. Select Microsoft Access Database on PC Files Server in the Standard data source drop-down menu, and then click Next.
5. Specify C:\temp\demo.accdb in the Database text box, and the host name of the machine running the SAS PC Files Server in the Server name text box. Click OK. A connection is made to the SAS PC Files Server, and the available tables are displayed in the next dialog.
6. Select Customers in the What table do you want to import? drop-down list, and then click Next.
7. Specify CUSTOMERS in the Member text entry field, and then click Next.
8. The SAS Import Wizard creates and submits PROC IMPORT code to import the ACCDB file, and you can save the code into a file. Specify the UNIX file location for the code, and then click Finish.
Verify that the ACCDB file was correctly imported:
NOTE: WORK.CUSTOMERS data set was successfully created.
NOTE: The data set WORK.CUSTOMERS has 20 observations and 10 variables.
View the SAS code generated by PROC IMPORT. It should look something like this:
PROC IMPORT OUT= WORK.customers
DATATABLE= "Customers"
DBMS=ACCESSCS REPLACE;
DATABASE="c:\temp\demo.accdb";
SERVER="PC-Files-Server-host-name";
PORT=9621;
SCANMEMO=YES;
USEDATE=NO;
SCANTIME=YES;
RUN;
Modify and use this code in your automated process.
Vince DelGobbo
SAS R&D
CSV would be the simplest. This is a plain text file with data delimited by commas. So you can read that in one any system, using datastep or proc import.
Access is Windows only, so you need to use something called pcfiles. There are details in the manual, assuming you have that licensed. Personally I would really avoid proprietary file formats, as it just adds adds in the overhead.
how to write this by code as csv is available on window environment & i have to import this in unix environment via sas.
I don't understand what you mean? CSV is a plain text file, you import it the same way on any operating system. Either datastep:
data want; infile "yourcsv.csv" dlm=","; length a 8 b $10; input a b $; run;
Plenty of examples out there.
Or via proc import:
proc import datafile="yourcsv.csv" out=want dbms=csv; run;
If you want to automate this, you need to solve two issues:
- the server needs to be able to access the file, so you either need a shared resource where you can put the file, or you need to transfer the file to the server, using tools like WinSCP or any other SSH-capable ftp tool.
- reading a delimited text file. You can start out and write the data step yourself, or you can use proc import to get data step code you can adapt and fine-tune.
well, if that is in unix environment then there is no issue atall. when i manually import then sas show the window location and if i write a sas code it only taked unix location. when i use infile when file is on window location then it prepare a file in temp then import it. Can't i do with the code.
So you need to solve my first point. Once you know the methods available to you (either how to make a file visible to the server or how to copy it there), we can look for ways to automate this.
Your network people should be able to help you in this regard.
lets say i transfered my msaccess.accdb file (version 2013), how can i import this in sas.
I'd rather use a text-based format like csv for data transfer between environments.
1. You'll run into issues transfering files between Windows and Unix, so it's best to keep it as text if possible.
2. To import via code, the file must be available to SAS on the server. You can upload the file as part of your process and then use a PROC IMPORT to read the data.
You need to license SAS/ACCESS to PC Files to have the ability to read/write Microsoft Access files:
SAS/ACCESS® 9.4 Interface to PC Files: Reference, Fourth Edition
http://go.documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.3&docsetId=acpcref&docsetTarget=ti...
If you want to read/write files from your UNIX environment, then you must license the software for that environment.
You also need the SAS PC Files Server running on a Windows-based machine:
Downloads: SAS PC Files Server
https://support.sas.com/downloads/package.htm?pid=2167
The UNIX and Windows machines must be able to communicate with one another.
Here are the steps to import the file in your UNIX environment (similar steps for Windows):
1. Place the Microsoft Access database file in C:\temp\ on the Windows machine. In this example I use the DEMO.ACCDB file from the SAS installation located at C:\Program Files\SASHome\SASFoundation\9.4\access\sasmisc\demo.accdb.
2. Start the SAS PC Files Server on the Windows machine.
3. Click File > Import Data on the UNIX machine to open the SAS Import Wizard.
4. Select Microsoft Access Database on PC Files Server in the Standard data source drop-down menu, and then click Next.
5. Specify C:\temp\demo.accdb in the Database text box, and the host name of the machine running the SAS PC Files Server in the Server name text box. Click OK. A connection is made to the SAS PC Files Server, and the available tables are displayed in the next dialog.
6. Select Customers in the What table do you want to import? drop-down list, and then click Next.
7. Specify CUSTOMERS in the Member text entry field, and then click Next.
8. The SAS Import Wizard creates and submits PROC IMPORT code to import the ACCDB file, and you can save the code into a file. Specify the UNIX file location for the code, and then click Finish.
Verify that the ACCDB file was correctly imported:
NOTE: WORK.CUSTOMERS data set was successfully created.
NOTE: The data set WORK.CUSTOMERS has 20 observations and 10 variables.
View the SAS code generated by PROC IMPORT. It should look something like this:
PROC IMPORT OUT= WORK.customers
DATATABLE= "Customers"
DBMS=ACCESSCS REPLACE;
DATABASE="c:\temp\demo.accdb";
SERVER="PC-Files-Server-host-name";
PORT=9621;
SCANMEMO=YES;
USEDATE=NO;
SCANTIME=YES;
RUN;
Modify and use this code in your automated process.
Vince DelGobbo
SAS R&D
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.