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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.