topic import csv or Access table from Window environment to Unix Environment in SAS Programming

import csv or Access table from Window environment to Unix Environment

Deepankar — Tue, 13 Mar 2018 12:04:11 GMT

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.

Re: import csv or Access table from Window environment to Unix Environment

RW9 — Tue, 13 Mar 2018 12:12:24 GMT

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.

Re: import csv or Access table from Window environment to Unix Environment

Deepankar — Tue, 13 Mar 2018 12:15:08 GMT

how to write this by code as csv is available on window environment & i have to import this in unix environment via sas.

Re: import csv or Access table from Window environment to Unix Environment

RW9 — Tue, 13 Mar 2018 12:18:04 GMT

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;

Re: import csv or Access table from Window environment to Unix Environment

Kurt_Bremser — Tue, 13 Mar 2018 12:23:01 GMT

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.

Re: import csv or Access table from Window environment to Unix Environment

Deepankar — Tue, 13 Mar 2018 13:16:52 GMT

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.

Re: import csv or Access table from Window environment to Unix Environment

Cynthia_sas — Tue, 13 Mar 2018 13:52:50 GMT

Hi: When you say you are using a Unix Environment, it would help to be specific. The answer to your question will depend on how you are using SAS on Unix. For example, are you actually using SAS University Edition, with shared folders? In this case, you MUST put the CSV file to import into your shared folder location and then you can import it with code using PROC IMPORT.

Or, if you are using SAS on Unix via SAS OnDemand for Academics, then you CANNOT read from your personal C: drive, but must upload your CSV file to the OnDemand folder location in your /home directory on the OnDemand cloud server and in this case, once uploaded, you can use PROC IMPORT to import the CSV file.

Or, do you have SAS on your company's Unix machine and your files on your personal computer's C: drive? In this case, whether you can run code to import depends entirely on whether the SAS server can read directly from your C: drive. @Kurt_Bremser explained how you would get the file to Unix if the server cannot read your C: drive directly.

cynthia

Re: import csv or Access table from Window environment to Unix Environment

Kurt_Bremser — Tue, 13 Mar 2018 14:00:54 GMT

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.

Re: import csv or Access table from Window environment to Unix Environment

Deepankar — Tue, 13 Mar 2018 14:13:23 GMT

lets say i transfered my msaccess.accdb file (version 2013), how can i import this in sas.

Re: import csv or Access table from Window environment to Unix Environment

Cynthia_sas — Tue, 13 Mar 2018 14:24:18 GMT

Hi:
Suggest you investigate whether you have the PC File server or not. There are a lot of examples in the documentation, such as these: http://go.documentation.sas.com/?docsetId=pcfsicg&docsetTarget=p1m2b3th113t84n1pt0vi70jj2ni.htm&docsetVersion=9.4&locale=en

cynthia

Re: import csv or Access table from Window environment to Unix Environment

Kurt_Bremser — Tue, 13 Mar 2018 14:27:28 GMT

I'd rather use a text-based format like csv for data transfer between environments.

Re: import csv or Access table from Window environment to Unix Environment

Reeza — Tue, 13 Mar 2018 15:18:09 GMT

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.

Re: import csv or Access table from Window environment to Unix Environment

Vince_SAS — Wed, 14 Mar 2018 12:32:30 GMT

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=titlepage.htm&locale=en

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

Re: import csv or Access table from Window environment to Unix Environment

branden — Thu, 24 May 2018 18:45:09 GMT

Thank you for the answer. This saved me a lot of time and worked wonderfully!