DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,636
Accepted Solution

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

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!


Accepted Solutions
Solution
‎03-24-2012 11:53 AM
Respected Advisor
Posts: 3,124

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

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

View solution in original post


All Replies
Solution
‎03-24-2012 11:53 AM
Respected Advisor
Posts: 3,124

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

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

New Contributor NL
New Contributor
Posts: 4

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

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

 

Super Contributor
Posts: 1,636

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

Hi Haikuo,

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

Linlin

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 1961 views
  • 0 likes
  • 3 in conversation