The SAS Output Delivery System and reporting techniques

Output proc print into excel tab

Reply
New Contributor
Posts: 3

Output proc print into excel tab

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.

 

Grand Advisor
Posts: 16,357

Re: Output proc print into excel tab

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.

 

 

 

 

Grand Advisor
Posts: 9,332

Re: Output proc print into excel tab

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;

....

New Contributor
Posts: 3

Re: Output proc print into excel tab

[ Edited ]

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;

 

Post a Question
Discussion Stats
  • 3 replies
  • 482 views
  • 0 likes
  • 3 in conversation