topic Re: Show result numbers with two decimal places in SAS Programming

Show result numbers with two decimal places

Gieorgie — Thu, 20 Jan 2022 08:04:56 GMT

I would like to show in the final table that I export to Excel numbers that have 2 decimal places, not 6 or 7. I tried by

sum(a.EXP_PIERWOTNA_NETTO) as  EAD_PRE_CCF format _numeric_ 9.3,

but isn't work. What is the best solution in this case ?

Re: Show result numbers with two decimal places

acordes — Thu, 20 Jan 2022 08:32:03 GMT

format=9.2

The _numeric_ is an automatically created macro variable for all numeric variables, mostly used in an array statement.

leave it and put a "=" between the format call and the specific format.

put 9.2 if you want 2 decimal places

Re: Show result numbers with two decimal places

Gieorgie — Thu, 20 Jan 2022 08:43:32 GMT

Hi, i did but still same nothing change sum(a.EXP_PIERWOTNA_NETTO) as EAD_PRE_CCF format = 9.2,

Re: Show result numbers with two decimal places

acordes — Thu, 20 Jan 2022 08:46:12 GMT

I suppose it's a snippet from a sql statement. Please show the complete code and verify that

EXP_PIERWOTNA_NETTO

is a numeric variable

Re: Show result numbers with two decimal places

acordes — Thu, 20 Jan 2022 08:48:35 GMT

proc sql;
select make, mean(horsepower) as avg_hp format=9.2 from 
sashelp.cars group by make;
quit;

that works

Re: Show result numbers with two decimal places

Kurt_Bremser — Thu, 20 Jan 2022 09:36:51 GMT

ALWAYS show us the whole log of your code when something does not work as expected; in your case, also include the code that transfers the data to Excel.

Re: Show result numbers with two decimal places

s_lassen — Thu, 20 Jan 2022 10:55:53 GMT

I do not think Excel will show the number of decimals that are transferred from SAS - if you enter e.g. "1.00" in Excel it will just show a "1". You should probably select the column in Excel and set the number of decimals there ("Format Cells").

Re: Show result numbers with two decimal places

ballardw — Thu, 20 Jan 2022 16:30:16 GMT

@Gieorgie wrote:

I would like to show in the final table that I export to Excel numbers that have 2 decimal places, not 6 or 7. I tried by
sum(a.EXP_PIERWOTNA_NETTO) as  EAD_PRE_CCF format _numeric_ 9.3,
but isn't work. What is the best solution in this case ?

You really need to show exactly how you create Excel from SAS. Many approaches leave the display of resulting values to default behavior of Excel, which is extremely likely to not be what you want. For example, if you use Proc Export then values are passed to the result, no formatting of any type is provided from SAS and everything is open to Excel interpretation.

Re: Show result numbers with two decimal places

Gieorgie — Fri, 21 Jan 2022 09:00:42 GMT

Hi thanks for your answer

This is part my code with proc sgl:

proc sql;
    create table COREP_CR_&gv_tbl_date as
	select distinct
	a.DATA_DANYCH as REPORTING_DATE,
	"" as ID,
	    sum(a.EXP_PIERWOTNA_NETTO) as  EAD_PRE_CCF,
            sum(a.KOREKTA) as  PROVISION,
from corep
;
quit;

This is my code to export to excel :

proc export data=COREP_CR_&gv_tbl_date
            outfile="&glb_path2./reports/mth_ak/COREP_CR_&gv_tbl_date..xlsx"
            dbms=xlsx
            replace;
            sheet="corep_cr";
run;

Re: Show result numbers with two decimal places

Kurt_Bremser — Fri, 21 Jan 2022 12:32:02 GMT

If adding

format=20.2

to your variables in the SQL step does not fix it, then the next step is using ODS EXCEL and a reporting procedure.

Note that your SQL might not create your intended result, as you calculate the total sums over the whole dataset and have these identical values in every combination of date/id. Did you miss to use a GROUP BY clause?

Re: Show result numbers with two decimal places

Gieorgie — Fri, 21 Jan 2022 14:11:31 GMT

I have a group, as I said, the code itself has 300 lines, so I did not put the ending sorry. So I understand I should add the format = 10.2 and then create the ODS EXCEL procedure?

Re: Show result numbers with two decimal places

Gieorgie — Fri, 21 Jan 2022 14:18:25 GMT

Ok, im trying to do like ODS EXCEL, when it generates the file it is created but empty

ods excel file = "&glb_path2./reports/mth_ak/COREP_CR_&gv_tbl_date..xlsx";  
proc print data=COREP_CR_&gv_tbl_date;

ods excel close;

Re: Show result numbers with two decimal places

Kurt_Bremser — Fri, 21 Jan 2022 18:22:47 GMT

@Gieorgie wrote:

Ok, im trying to do like ODS EXCEL, when it generates the file it is created but empty
ods excel file = "&glb_path2./reports/mth_ak/COREP_CR_&gv_tbl_date..xlsx";  
proc print data=COREP_CR_&gv_tbl_date;

ods excel close;

You have no RUN statement to create a step boundary, and global statements (like ODS) are executed immediately when encountered, so the Excel destination is closed before PROC PRINT writes any output to it.

See Maxim 49.