BookmarkSubscribeRSS Feed
juliajulia
Obsidian | Level 7

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                            
4 REPLIES 4
Cynthia_sas
SAS Super FREQ
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
Obsidian | Level 7
thank you. what is the summarized table will looks like. would you explain more.
Cynthia_sas
SAS Super FREQ

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:

Cynthia_sas_0-1655413479885.png

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:

Cynthia_sas_1-1655413827324.png

 

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:

Cynthia_sas_2-1655414052664.png

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
Obsidian | Level 7

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.

 

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 1136 views
  • 1 like
  • 2 in conversation