Learning SAS? Welcome to the exclusive online community for all SAS learners.

how to export data specified columns sas to excel

Reply
Contributor
Posts: 42

how to export data specified columns sas to excel

Hi firends 

      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

Super User
Super User
Posts: 7,955

Re: how to export data specified columns sas to excel

Posted in reply to tlnarayana26

Hi,

Please check the search functionality, this has come up at least 2 or 3 times in the last day or two, here is the latest one:

Contributor
Posts: 42

Re: how to export data specified columns sas to excel

Hi sir

    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.

Super User
Super User
Posts: 7,955

Re: how to export data specified columns sas to excel

Posted in reply to tlnarayana26

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.

Contributor
Posts: 43

Re: how to export data specified columns sas to excel

Posted in reply to tlnarayana26

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 ;

run ;

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

Ask a Question
Discussion stats
  • 4 replies
  • 474 views
  • 0 likes
  • 3 in conversation