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

[Linlin]

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!

[Haikuo · Accepted solution]

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

[NL]

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

[Linlin]

Hi Haikuo,

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

Linlin