topic Re: Stored Process SAS Dollar format issue in Developers

Stored Process SAS Dollar format issue

BhararaTej — Mon, 20 Aug 2018 18:28:46 GMT

Hi,

Looking to get some help here. I am using stored process to output data into excel and the stored process is published through a SAS BI portal. I am using a Proc Print procedure below, but the problem is that everytime it prints in excel with dollar format with 2 decimal inspite of the fact that I dont give options to print out decimal.

data _null_;
rc = stpsrv_header('Content-disposition','attachment; filename= XYZ.xls' );
run;

%let _ODSDEST=tagsets.ExcelXP;
%let _ODSSTYLE=Seaside;
%let _ODSOPTIONS=%str(options(sheet_name="ABC" frozen_header="3" start_at="1,3" frozen_rowheaders="3" ));

***********************************;

%stpbegin;

proc print data = spds.llp_V2 noobs LABEL style(header)= {background=AQUAMARINE};

FORMAT

B0 DOLLAR17.

LABEL
B1 = D1 B2 = D2
B3 = D3 B4 = D4

B5 = D5 B6 = D6
B7 = D7 B8 = D8
B9 = D9 B10 = D10

B11 = D11 B12 = D12
B13 = D13 B14 = D14
B15 = D15 B16 = D16
B17 = D17 B18 = D18
B19 = D19 B20 = D20
B21 = D21 B22 = D22
B23 = D23 B24 = D24
B25 = D25 B26 = D26
B27 = D27 B28 = D28
B29 = D29 B30 = D30
B31 = D31
ORG_NAME='YEARMO-ORG'
B0='TOTAL OS';

run;

The output of B0 still comes like $1978568.00 and I want it to be just $1978568

Thanks,

Re: Stored Process SAS Dollar format issue

Kurt_Bremser — Mon, 20 Aug 2018 18:21:48 GMT

Is it BO or B0?

Re: Stored Process SAS Dollar format issue

BhararaTej — Mon, 20 Aug 2018 18:28:21 GMT

It is B0 (Zero)

Re: Stored Process SAS Dollar format issue

Tom — Mon, 20 Aug 2018 18:33:34 GMT

There is nothing in your program to tell Excel how to format that column. The SAS format is just telling SAS how to format the information it writes into the XML file that you are generating. You need to use STYLE options to tell EXCELXP what excel formatting instructions to add to the XML file.

Re: Stored Process SAS Dollar format issue

BhararaTej — Mon, 20 Aug 2018 19:01:10 GMT

Thanks Tom.

I havent tried that before specifcally using format with the style option. I have although tried changing colors before using that option

VAR ORG_NAME /style(data)={color=BROWN};

Can you provide an example of the syntax for changing the format to Dollar7. without any decimals.

Thanks!

Re: Stored Process SAS Dollar format issue

BhararaTej — Mon, 20 Aug 2018 19:02:29 GMT

It is B0 (Zero).

Thanks

Re: Stored Process SAS Dollar format issue

Tom — Mon, 20 Aug 2018 19:17:20 GMT

Use the tagattr attribute.

Here is your exact issue in another question:

https://communities.sas.com/t5/ODS-and-Base-Reporting/Currency-Format-Without-Decimal-Fraction-When-Output-to-Excel-By/td-p/18055

Re: Stored Process SAS Dollar format issue

BhararaTej — Mon, 20 Aug 2018 19:41:52 GMT

Thanks Tom