10-27-2016 01:37 PM
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.
10-27-2016 02:22 PM
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.
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.
10-28-2016 12:00 AM
Yes. Some skeleton code could like:
ods excel options(sheet_interval='bygroup' , sheet_name='#byval1');
proc report data=class(where=(teacher='Arthur')) nowd;
10-28-2016 11:06 AM - edited 10-28-2016 11:07 AM
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';
var FA name week ENROLL_DT END_DT SCHOOLDAYS DAYS_PRESENT DAYS_ABSENT ATTRATE;