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
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.
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.
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.
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.