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!
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
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
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
Hi Haikuo,
Thank you very much for your answer and explanation! It worked perfectly.
Linlin
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.