DATA Step, Macro, Functions and more

import csv or Access table from Window environment to Unix Environment

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

import csv or Access table from Window environment to Unix Environment

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.


Accepted Solutions
Solution
‎03-21-2018 07:09 AM
SAS Super FREQ
Posts: 343

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

Posted in reply to Deepankar

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

 

View solution in original post


All Replies
Super User
Super User
Posts: 9,203

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

Posted in reply to Deepankar

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.

Contributor
Posts: 21

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

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

Super User
Super User
Posts: 9,203

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

Posted in reply to Deepankar

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;
Super User
Posts: 9,567

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

Posted in reply to Deepankar

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 21

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

Posted in reply to KurtBremser

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. 

SAS Super FREQ
Posts: 9,257

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

Posted in reply to Deepankar
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. @KurtBremser explained how you would get the file to Unix if the server cannot read your C: drive directly.

cynthia
Super User
Posts: 9,567

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

Posted in reply to Deepankar

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 21

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

Posted in reply to KurtBremser

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

SAS Super FREQ
Posts: 9,257

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

Posted in reply to Deepankar
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&docs...

cynthia
Super User
Posts: 9,567

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

Posted in reply to Deepankar

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 22,844

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

Posted in reply to Deepankar

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.

Solution
‎03-21-2018 07:09 AM
SAS Super FREQ
Posts: 343

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

Posted in reply to Deepankar

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 139 views
  • 4 likes
  • 6 in conversation