03-26-2015 08:52 AM
i have a problem exporting data.
i have two different files.
1 st is excel file .
its containg columns are id name jan15 dec14 nov14 and oct14 .
2 nd is sas data set.
its containg columns are id name feb15 and mar15.
sas data set is export to excel. (not using dde)
here feb15 and mar15 columns are export to excel in between name and jan15.
plz help me
03-26-2015 09:11 AM
i want export the mar15,feb15 columns from sas data set to excel file (1st file ) in between name and jan15.
here 1st one is excel file .this file is alredy exist.
insert insering two columns (mar15,feb15) b/w the name and jan15.
03-26-2015 09:22 AM
Ah, ok. Then probably not. You can try to use the libname to excel statement, that may allow you to insert columns and re-order, though probably not easy to use. I would suggest to get all your data into SAS, create the table as you want it to look like and then export it to Excel. Remember SAS != Excel, SAS is a fixed structure table based system for structured data processing. Excel is not, its a free for all.
The only other way I can think of is to export your data from SAS to CSV, then in your original Excel file write some VBA script to open and process the CSV data.
03-26-2015 09:38 AM
You need to first merge your two parts of data together, and then you can use a Retain statement to control the order of the columns.
(You will need to first import the excel data into a sas dataset, I am calling oldmonthdata below, and of course, you need to sort the datasets by id before you merge.)
Data finaldata ;
retain id name mar15 feb15 jan15 dec14 nov14 and oct14 ;
merge oldmonthdata newmonthdata ;
by id ;
and then export the sas to Excel ;
p.s. - export to a new Excel file, or specify a new sheet in the existing file. I have had problems when trying to overwrite a sheet with changed columns.
Message was edited by: Carla Wilson