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.
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.
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;
....
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.