BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Deepankar
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Vince_SAS
Rhodochrosite | Level 12

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

13 REPLIES 13
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Deepankar
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Kurt_Bremser
Super User

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.

Deepankar
Calcite | Level 5

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. 

Cynthia_sas
SAS Super FREQ
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
Kurt_Bremser
Super User

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.

Deepankar
Calcite | Level 5

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

Cynthia_sas
SAS Super FREQ
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
Reeza
Super User

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.

Vince_SAS
Rhodochrosite | Level 12

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

 

branden
Calcite | Level 5
Thank you for the answer. This saved me a lot of time and worked wonderfully!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 2602 views
  • 7 likes
  • 7 in conversation