topic Re: Output proc print into excel tab in ODS and Base Reporting

Output proc print into excel tab

ablack — Thu, 27 Oct 2016 17:37:22 GMT

Hi. So I'm working with a file that contains hierarchical student level data, with one row per week. I want to export my data to excel. Ideally I would create one excel file per teacher, and one tab per student, data for each week presented on each row. I can easily sort by teacher and student, then do a proc print, by student. I get exactly what i need in SAS. I just can't figure out how to get those tables into multiple tabs in excel. And thoughts? I'm thinknig I need to use ODS, or soem sore of do loop to create mulltiple small data sets. BUt I'm hoping that there is a SAS feature I'm not familiar with that will simply export my proc print output.

Re: Output proc print into excel tab

Reeza — Thu, 27 Oct 2016 18:22:40 GMT

You're correct. ODS is probably your best bet. Depending on your version it may be ODS EXCEL or TAGSETS.EXCELXP

Look at the Data Driven workbook examples here.

https://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html

If you want multiple files unfortunately you need a macro. Develop your base code first - ie get it working for ONE teacher using a WHERE filter on your proc prints and then post the code and we can help make it a macro.

Re: Output proc print into excel tab

Ksharp — Fri, 28 Oct 2016 04:00:28 GMT

Yes. Some skeleton code could like:

options nobyline;

ods excel options(sheet_interval='bygroup' , sheet_name='#byval1');

proc report data=class(where=(teacher='Arthur')) nowd;

by student;

....

Re: Output proc print into excel tab

ablack — Fri, 28 Oct 2016 15:07:42 GMT

Thank you! Here's the code I am using and it works beautifully. All is need is one proc print per teacher.

proc sort data=data.SDR_CUMULATIVE;

BY school fa name week;

ods listing close;

ods tagsets.ExcelXP path='U:\Data Team\2 Dashboards\1 Weekly\FY 17\reports' file='DA_Medina.xml'

style = daisy;

* Create a separate worksheet for each of Medina's Students;

proc print data=data.SDR_CUMULATIVE noobs label split='*';

where SCHOOL = 'DA' and FA = 'Medina';

by NAME;

var FA name week ENROLL_DT END_DT SCHOOLDAYS DAYS_PRESENT DAYS_ABSENT ATTRATE;

run; quit;