09-13-2014 12:32 PM
HI SAS Folks,
I need a summarized report like this for my further comparison studies in the education department-. Can anybody help me with summarisation either using datastep, proc means or proc summary results that is macrotised and reusable to be exported to excel as it looks below in output? There are different datasets for each subject like, for example science dataset has variables Maths, Physics etc and marks as values along with a variable year with 'year values'/.
So Input looks like this for a Science table:
Output needed as all parent subjects in one as follows after exported in excel:
|Total Science marks||357||384|
|Total Arts Marks||252||230|
NOTE: The number of datasets keep increasing as number of subjects increases and they are fairly large as they contain the last 15 years data(1999-2013).
09-13-2014 01:33 PM
You need to transpose your data and then a simple proc report or proc tabulate will generate the report you need.
If you're dealing with multiple tables I'd probably make the transformation a macro and then sandwich the outputs between ODS ExcelXP.Tagsets or ODS EXCEL (if SAS 9.4) to generate the Excel file.
input maths physics biology chemistry applied_science year;
56 67 78 65 91 2013
85 78 65 98 58 2012
select name into :vlist separated by " "
where libname='WORK' and upcase(memname)="HAVE"
and upper(name) ne 'YEAR';
array vlist(*) &vlist;
do i=1 to dim(vlist);
keep Subject Value Year;
proc tabulate data=want;
class year subject;
table Subject='Science Subjects' All='Total Science Marks',
09-13-2014 11:35 PM
Thank you, I have multiple input tables because each subject line is a separate input table as you have demonstrated the code for Science as input table nicely. I have got SAS enterprise guide and I suppose ODS excel should work similarly right?
And, ideally in the excel report, science report needs to be followed by Arts report and so on(other subject reports) in the same excel sheet. So is this where a transformation macro needs to be applied?