Format results of Pro Freg

[Tri_Luong]

Hi everyone,

I am trying to use the scripts below:

proc freq data=work.loan_overdue;
     tables report_date/nopercent nocum frmat=comma16.0;
     weight 'total_amount';
run;

however, the results showed that:

REPORT DATE        Freq

01/08/2018              5.702E10              

02/08/2018              5.881E10

03/08/2018              6.378E10

04/08/2018              6.198E10

05/08/2018              5.979E10

Could you please help to instruct me how to:

1. show the format of Freq in "#,###.##" (for eg. 15,244,463,123.12)?

2. show the Freq with unit Million. (for eg. 15,244.46)?

Thank everyone.

[Astounding · Accepted solution]

The most flexible way would be to create an output data set instead of a report.  For example, change the TABLE statement to:

tables report_date / noprint out=want (keep=report_date count);

That gives you a data set named WANT with the variables REPORT_DATE and COUNT.  You could easily use that in a DATA step, and divide the COUNT values by 1,000,000 before printing.  And you could easily apply a format for printing COUNT.

[Tri_Luong]

Thank Astounding for your prompt feedback. However, is there any way to change the format directly on the report instead of creating new data set?

[Astounding]

I suppose you could always diivide your weight values by 1,000,000 before running PROC FREQ.

[Tri_Luong]

Hi Astounding,

Did you mention that I should create one more variable which is "new value" = "original_value"/1,000,000 before proc freq with weight for "new value"? If I don't want to create new variable, what should I do?!

[Astounding]

You could create it within a view, and make the view temporary.  For example:

data temp / view=temp;

set work.loan_overdue;

total_amount = total_amount / 1000000;

run;

Then use the view in PROC FREQ:

proc freq data=temp;

The rest stays the same, and the VIEW is temporary, stored in the WORK area.  It doesn't save a copy of your data, just a set of instructions for how to process the data.