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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: