- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi, i did but still same nothing change sum(a.EXP_PIERWOTNA_NETTO) as EAD_PRE_CCF format = 9.2,
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
proc sql;
select make, mean(horsepower) as avg_hp format=9.2 from
sashelp.cars group by make;
quit;
that works
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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").
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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.