Desktop productivity for business analysts and programmers

Send multiple SAS data sets to one Access Database / Excel Workbook in SAS Enterprise Guide

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Send multiple SAS data sets to one Access Database / Excel Workbook in SAS Enterprise Guide

Hello all!

 

I want to be able to export more than 1 data set to an Access database (or as separate sheets in an Excel file) but I can't figure out how to do it! Can anyone help me?

 

Thanks

Marcus Peters


Accepted Solutions
Solution
‎10-10-2017 05:58 AM
Super User
Super User
Posts: 9,227

Re: Send multiple SAS data sets to one Access Database / Excel Workbook in SAS Enterprise Guide

Posted in reply to Marcus_Peters

Its generally a good idea to search for answers before posting.  There are numerous methods to get data out to both Access and Excel.  Lets take Excel first:

tagsets.excelxp - this creates XML text file which Excel can read and parse.

libname excel (SAS 9.4), this can create/access XLSX files directly

proc export - can create xls files

To CSV - text file with comma separation which Excel can also read

 

With Access you can also do:

libaname access - never used myself

proc sql pass through

proc export

etc.

View solution in original post


All Replies
Solution
‎10-10-2017 05:58 AM
Super User
Super User
Posts: 9,227

Re: Send multiple SAS data sets to one Access Database / Excel Workbook in SAS Enterprise Guide

Posted in reply to Marcus_Peters

Its generally a good idea to search for answers before posting.  There are numerous methods to get data out to both Access and Excel.  Lets take Excel first:

tagsets.excelxp - this creates XML text file which Excel can read and parse.

libname excel (SAS 9.4), this can create/access XLSX files directly

proc export - can create xls files

To CSV - text file with comma separation which Excel can also read

 

With Access you can also do:

libaname access - never used myself

proc sql pass through

proc export

etc.

New Contributor
Posts: 3

Re: Send multiple SAS data sets to one Access Database / Excel Workbook in SAS Enterprise Guide

RW9, I can't make these work in SAS EG (already tried) as it doesn't know I have access to any shared drives being a server-based session? I need a solution for an EG workflow such as writing one of the Export routines built into that platform?
Super User
Super User
Posts: 9,227

Re: Send multiple SAS data sets to one Access Database / Excel Workbook in SAS Enterprise Guide

Posted in reply to Marcus_Peters

Then your problem is that your server based session does not have access to the particular areas you need to access.  This is therefore an issue for your IT group to sort out for you so that you can access those areas, nothing to do with the type of SAS code you use to create.  It may require you to use an FTP client to transfer back and forth, and if you server session is not windows with office installed, then you would need either an open source variant - XML/CSV or PCFILES.  Either way, not sure how we can help with your infrastructure.

New Contributor
Posts: 3

Re: Send multiple SAS data sets to one Access Database / Excel Workbook in SAS Enterprise Guide

Thanks, that's a good clear response! I will talk to the technical architecture guys and see if they can help me out.
SAS Employee
Posts: 199

Re: Send multiple SAS data sets to one Access Database / Excel Workbook in SAS Enterprise Guide

Posted in reply to Marcus_Peters

Hi @Marcus_Peters

 

Hopefully one of those solutions will satisfy you.

 

 

For excel format, you can use for instance ODS EXCEL or the ExcelXP tagset such as:

ods excel file='c:\data\output1.xlsx';
proc print data=sashelp.class; run;
proc print data=sashelp.cars; run;
ods excel close;
 
ods tagsets.ExcelXP file='c:\data\output_tagset.xls';
proc print data=sashelp.class; run;
proc print data=sashelp.cars; run;
ods tagsets.ExcelXP close;

As previously discussed with that SAS Communities post Exporting Multiple Data Sets to Multiple Worksheets in one Excel Workbook, proc export is another option (if you have the SAS/ACCESS Interface to PC Files module).

 

You can use the proc export in order to export to Microsoft Access as well.

Another alternative found on SAS Communities again is Exporting Multiple SAS datasets to Access Database.

 

 

Cheers,
Damo

☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 195 views
  • 3 likes
  • 3 in conversation