topic Re: Can I update an excel file with a SAS dataset without import the excel file to SAS? in SAS Programming

Can I update an excel file with a SAS dataset without import the excel file to SAS?

Linlin — Sat, 24 Mar 2012 14:32:19 GMT

Hi All,

I have an excel file with two columns. Can I update my exel file with a sas dataset without convert the excel file to SAS file?

My excel file looks like:

name age

AA 20

BB 30

CC 40

DD 50

My sas file:

name sex

AA male

DD female

I want the updated excel look like this:

name age sex

AA 20 male

BB 30

CC 40

DD 50 female

I have sas 9.3. Below is the log file after I ran "proc setinit;run";

Product expiration dates:

---Base SAS Software

14AUG2012

---SAS/STAT

14AUG2012

---SAS/GRAPH

14AUG2012

---SAS/ETS

14AUG2012

---SAS/FSP

14AUG2012

---SAS/OR

14AUG2012

---SAS/AF

14AUG2012

---SAS/IML

14AUG2012

---SAS/QC

14AUG2012

---SAS/SHARE

14AUG2012

---SAS/LAB

14AUG2012

---SAS/ASSIST

14AUG2012

---SAS/CONNECT

14AUG2012

---SAS/INSIGHT

14AUG2012

---SAS/EIS

14AUG2012

---SAS/GIS

14AUG2012

---SAS/SHARE*NET

14AUG2012

---MDDB Server common products

14AUG2012

---SAS Integration Technologies

14AUG2012

---SAS/Secure Windows

14AUG2012

---SAS Enterprise Guide

14AUG2012

---SAS Bridge for ESRI

14AUG2012

---OR OPT

14AUG2012

---OR PRS

14AUG2012

---OR IVS

14AUG2012

---OR LSO

14AUG2012

---SAS/ACCESS Interface to DB2

14AUG2012

---SAS/ACCESS Interface to Oracle

14AUG2012

---SAS/ACCESS Interface to Sybase

14AUG2012

---SAS/ACCESS Interface to PC Files

14AUG2012

---SAS/ACCESS Interface to ODBC

14AUG2012

---SAS/ACCESS Interface to OLE DB

14AUG2012

---SAS/ACCESS Interface to Teradata

14AUG2012

---SAS/ACCESS Interface to MySQL

14AUG2012

---SAS/IML Studio

14AUG2012

---SAS Workspace Server for Local Access

14AUG2012

---SAS/ACCESS Interface to Netezza

14AUG2012

---SAS/ACCESS Interface to Aster nCluster

14AUG2012

---SAS/ACCESS Interface to Greenplum

14AUG2012

---SAS/ACCESS Interface to Sybase IQ

14AUG2012

---DataFlux Trans DB Driver

14AUG2012

---SAS Framework Data Server

14AUG2012

---Reserved for Dataflux

14AUG2012

Thank you!

Re: Can I update an excel file with a SAS dataset without import the excel file to SAS?

Haikuo — Sat, 24 Mar 2012 15:52:59 GMT

LinLin,

This answer is very much subjected to errors like many of my other answers.

But what I can tell you is that if you want a direct yes/no answer for your specific question, you would not be happy.

However,there are some walk-arounds that you possibly don't mind living with.

What you are asking is an in-situ update, which means NO import - export involved. This would inevitably leads to the only possible answer: Modify statement. The catch is: Modify statement can NOT add in new variables because of this in-situ nature. (unlike update, merge, set etc, during which, a invisible copy is made,so that you can add or drop variables).

By this far I think you already can figure out yourself: you need another empty column'sex' in your excel files. Here are some sample codes:

/*you will needto add 'sex' column*/

libname myexl "c:\temp\have.xls" scan_text=no;

data trans ;

input(name sex) (:$);

cards;

AA male

DD female

;

data myexl.'sheet1$'n;

modify myexl.'sheet1$'n trans ;

by name;

run;

/*to remove thelibref, so you can open the spreadsheet just being updated*/

libname myexl;

Regards,

Haikuo

Re: Can I update an excel file with a SAS dataset without import the excel file to SAS?

Linlin — Sat, 24 Mar 2012 17:22:47 GMT

Hi Haikuo,

Thank you very much for your answer and explanation! It worked perfectly.

Linlin

Re: Can I update an excel file with a SAS dataset without import the excel file to SAS?

NL — Fri, 23 Jun 2017 03:47:45 GMT

Hi Haikuo,

Can the same work in v9.4 if my excel file is in xlsx format (MS Office 2101)? I have the option to read the Excel file spreadsheet using libname with the xlsx engine. My objective is to update a column in a spreadsheet based on a table in SAS and keep all other existing columns intact.

Regards,

Nelson