Need help in creating getting the report in the needed format?

Reply
Contributor
Posts: 70

Need help in creating getting the report in the needed format?

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:

mathsphysics biology chemistryapplied scienceyear
56677865912013
85786598582012

Output needed as all parent subjects in one as follows  after exported in excel:

20132012
AustraliaAustralia
Science subjects
Maths5685
physics6778
chemistry7865
Biology6598
applied science9158
------------------------------------------------
Total Science marks357384
========================
Arts Subjects
European Studies7564
Art History9098
Linguistics8768
------------------------------------------------
Total Arts Marks252230

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).

Thanks,

Mark

Grand Advisor
Posts: 17,393

Re: Need help in creating getting the report in the needed format?

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.

data have;

input maths physics biology chemistry applied_science year;

cards;

56 67 78 65 91 2013

85 78 65 98 58 2012

;

run;

proc sql;

  select name into :vlist separated by " "

  from sashelp.vcolumn

  where libname='WORK' and upcase(memname)="HAVE"

  and upper(name) ne 'YEAR';

quit;

data want;

set have;

array vlist(*) &vlist;

do i=1 to dim(vlist);

  Subject=vlabel(vlist(i));

  Value=vlist(i);

  Output;

end;

keep Subject Value Year;

run;

proc tabulate data=want;

  class year subject;

  var value;

  table Subject='Science Subjects' All='Total Science Marks',

  year=''*value='Australia'*sum='';

run;

Contributor
Posts: 70

Re: Need help in creating getting the report in the needed format?

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?

Thanks,

Mark

Ask a Question
Discussion stats
  • 2 replies
  • 729 views
  • 0 likes
  • 2 in conversation