proc tabulate formatting

[Q1983]

proc sql;

create table action as

select distinct Region,count(ln_no) as ln, sum(balance) as bal

from main;

where region = 'West';

quit;

Sample output is

Region             ln                          bal

West           2500                     2300000

I created a proc tabulate with the intention of capturing both the count of the ln_no and the sum of the balance

Proc tabulate data=action order= data format=10. S=[cellwidth=150];

  Class Region;
  Var ln;
  Table Region=' ' all={label='Grand Total' S=[background = lightblue cellwidth=160]} *[STYLE=[Font_Weight=BOLD]],
        Region='Status '*ln=' '*sum=' '
        all={label='Grand Total' S=[background = lightblue]} *[STYLE=[Font_Weight=BOLD]] *ln=' '*sum=' ' / box='Investor Group';
  TITLE 'Loan Status';

My results give me a summary of the count of the loan however I am not getting the total or sum of the balance.  Essentially I am getting the 2500 but not the 2300000.  In addition how would I format the bal so it reads as a dollar format such as $2,300,000.00

[Cynthia_sas]

Hi:

All the variables you are going to use in the TABLE statement must be listed in the VAR and/or CLASS statements. So, I do not see BAL in the VAR statement -- you won't get a sum on BAL if it's not in both the VAR statement and in the TABLE statement. In addition, it is possible that you do not need to use the pre-summarized dataset ACTION in your PROC TABULATE, but you can just use MAIN directly with PROC TABULATE. However, since you haven't posted any data, it's hard to come up with a sample program. But the need to have BAL in the VAR and TABLE statement is a fundamental part of how TABULATE works.

cynthia

[Q1983]

Thank for the post.  I can now get both statistics.  The output will be ODS XML.  Here is a part of the code

ODS LISTING CLOSE;

ODS TAGSETs.ExcelXP file="&ReportOut..xml" Path="&OutDir" style=&standard;

title;

footnote;

ODS TAGSETS.ExcelXP

   options(sheet_interval='none'

           default_column_width='8'

           sheet_name="File1"

           center_horizontal="no"

           Orientation='Landscape'

           embedded_titles='Yes'

           fittopage="No"

           blackandwhite="No"

           Embedded_Footnotes='Yes'

           autofit_height="Yes");

The only issue now is getting the total to display in dollar15.2 format.  In my dataset It formats correcty but is not being passed to ODS output

proc sql;

create table action as

select distinct Region,count(ln_no) as ln, sum(balance) as bal format=dollar15.2.

from main;

where region = 'West';

quit;

Sample output is

Region             ln                          bal

West           2500                     $2,300,000.00

The balance displays correctly in the proc sql statement.  However in the proc tabulate statement it shows as 23000000

[ballardw]

You can specify the format to display a value in the table statement such as (assuming the rest of the table statement as desired)

Balance*sum*f=dollar15.2 will display the sum of balance with the dollar15.2 format.

You didn't post a second tabulate so where to put elements along with your code isn't possible.

You'll also find that tagsets.excelxp will need a tag_attr setting as EXCEL picks a display format.

[Cynthia_sas]

Hi, It is TAGATTR style attribute override (not tag_attr). Microsoft Excel is notorious for using the Excel defaults instead of the formats, and decimal places that you take the time to create with SAS. You have to use TAGATTR to send a Microsoft format from SAS to Excel. Have a look at this paper (http://support.sas.com/resources/papers/proceedings11/266-2011.pdf).

cynthia