how to remove the second table title from proc report result

[juliajulia]

all experts:

would you please help me take a look at my code to see how i can create a subtotal for each table and stack those tables from proc report result, how can i get rid of the second table's title and the 'N', '%' and only have one title and 'N' and  '%' at the top of the table. here is the sas code i am using:

ods excel options(sheet_name="test" sheet_interval="none" )
file = "xx\test.xlsx";
proc report data = data.allrsr nowd;

column age
year,( n pctn)
;
define age/group id order=internal;
define year/across ;
define n / format =8. "N";
define pctn / "%" format =percent7.1;

proc report data=data.allrsr nowd;
column raceeth year,( n pctn);
define raceeth/group id order=internal;
define year/across ;
define n / format =8. "N";
define pctn / "%" format =percent7.1;

run;
ods excel close;

the table that i want to create will looks like this:

Table 1a. title

2016

2017

2018

2019

2020

N

%

N

%

N

%

N

%

N

%

Age group (yr)

<13

13–14

15–19

20–24

25–29

30–34

35–39

40–44

45–49

50–54

55–59

60–64

≥65

Subtotal

Race/ethnicity

American Indian/Alaska Native

Asian

Black/African American

Hispanic/Latinoa

Native Hawaiian/Pacific Islander

White

Multiple races

Subtotal

[Cynthia_sas]

Hi:
The usual way to do something like this is to create your 2 separate summarized datasets and then make a generic "Column1" variable and generic "section" variable. Your AgeGroup table would be section 1 and your race/ethnicity table would be section 2. You could use other procedures like PROC FREQ or even PROC REPORT to summarize the data and then you could have one final PROC REPORT that sent the output to Excel.
Cynthia

[juliajulia]

thank you. what is the summarized table will looks like. would you explain more.

[Cynthia_sas]

Hi:

  Since you did not post data, I made some fake data using SASHELP.CLASS. That also meant I had to make some rows for each year in order to have a year variable. So here's an example of some fake starting data:

This is only partial data. I have a STATUS variable that is character and an AGE variable that is numeric and I want the N and PCTN for each year. Of course, all my data is the same, so all my N and PCTN will be the same for every year. But if I summarize my data using PROC REPORT, I can also use PROC REPORT to create the SECTION and COL1 variable. In this screen shot, I show the 2 separate PROC REPORTS with the output data set that was created by PROC REPORT for each section:

Notice how the PROC REPORT "across" variables for YEAR have been renamed in the output data sets into _C2_ _C3_ for the 2018 numbers, _c4_ and _c5_ for 2019 and _c6_ and _c7_ for the 2020 numbers. 

  Since you already have the 2 separate PROC REPORTS, you'd only need to add an OUT= option to the PROC REPORT statements in order to get  a pre-summarized data set created with the N and the PCTN.

  Next, the 2 datasets are combined using a DATA step program, where the _col#_ names are turned into something more meaningful and I've also created a ROWORD helper variable:

Then, the final PROC REPORT is just shown as HTML output above for testing. There is a subtotal line after every section, the header information for which variable is inserted above each section and a blank line was added after each section. I did not blank out the col1 header variable because I wanted you to see how that variable was used. The SECTION variable is used by PROC REPORT, but is not visible because I used the NOPRINT Option.

  All the code that produced these screen shots is below.

Cynthia

** here's the code;

** make some fake data from sashelp.class;
** all N and PCTN will be the same for every year because...fake data;
data fakedata;
  length status $15;
  set sashelp.class;
  if age ge 14 then status = 'High School';
  else if age in (11,12,13) then status = 'Middle School';
  do year = 2018 to 2020 by 1;
    output fakedata;
  end;
run;

proc print data=fakedata;
title '1) what does fake data look like';
run;


title;
** create section 1 n/pctn for age;
** also create a section and a col1 variable;
** since AGE is numeric and STATUS is character;
** COL1 needs to be created as character using a PUT function;
proc report data = fakedata out=section1;
title '2) PROC REPORT summarizes data for AGE variable';
column  age  year,( n pctn) section col1;
define age/group id;
define year/across ;
define n / format =8. "N";
define pctn / "%" format =percent8.1;
define section / computed;
define col1 / computed;
compute section /character length=40;
   Section = 'Section1:Age Group (yr)';
endcomp;
compute col1/character length=20;
  col1 = put(age,best2.);
endcomp;
run;


proc print data=work.section1;
title '3) Before combining: summarized section1 data';
run;


** create section 2 n/pctn for every status ;
** also create a section and a col1 variable;
proc report data=fakedata out=section2;
title '4) PROC REPORT summarizes data for STATUS variable';
column status year,(n pctn) section col1;
define status/group id;
define year/across ;
define n / format =8. "N";
define pctn / "%" format =percent8.1;
define section / computed;
define col1 / computed;
compute section /character length=40;
   Section = 'Section2:School Status';
endcomp;
compute col1/character length=20;
  col1 = status;
endcomp;
run;

proc print data=work.section2;
title '5) Before combining: summarized section2 data';
run;

data final;
  set section1(in=inone) section2(in=intwo);
  drop _c2_ _c3_ _c4_ _c5_ _c6_ _c7_ _BREAK_;
  roword = _n_;
  N18 = _c2_;
  P18 = _c3_;
  N19 = _c4_;
  P19 = _c5_;
  N20 = _c6_;
  P20 = _c7_;
run;

** show results of combining the 2 sections together;
proc print data=final;
title '6) FINAL combined data before PROC REPORT';
var section roword col1 n18 p18 n19 p19 n20 p20;
run;


** this final PROC REPORT output could be sent to ODS EXCEL or PDF or RTF;
** I just used the default HTML destination for testing;
title '7) Final PROC REPORT can send to ODS Excel with subtotals and headers';
title2 'using combined summary info';
proc report data=final;
  column section roword col1 ('2018' N18 P19) ('2019' N19 P19) ('2020' N20 P20);
  define section / group noprint;
  define roword / order noprint;
  define col1 / display;
  define N18 / analysis 'N';
  define P18 / analysis f=percent8.1 'PCTN';
  define N19 / analysis 'N';
  define P19 / analysis f=percent8.1 'PCTN';
  define N20 / analysis 'N';
  define P20 / analysis f=percent8.1 'PCTN';
  break after section / summarize;
  compute before section/style={just=l textdecoration=underline font_weight=bold};
     tmpvar = scan(section,2,':');
	 line tmpvar $40.;
  endcomp;
  compute after section;
    col1 = 'SubTotal';
	line ' ';
  endcomp;
run;

[juliajulia]

Thank you so much for your help. the code works well but i just need to digest a little bit and make it works on my data.