topic Re: Number Ranges in Excel in ODS and Base Reporting

Number Ranges in Excel

saspert — Tue, 16 Aug 2011 15:24:47 GMT

Hello,

I have a requirement to display number ranges in Excel but Excel is treating them as numbers and converting them to a date format.

1-5 converted to 5-Jan

6-10 converted to 10-Jun

11-14 converted to 14-Nov

The temporary workaround I put in place was to display them with a single quote between the opening double quote and 1. But the single quote also shows up on the Excel and PDF output. Is there a better way?

PROC SQL;

CREATE TABLE ACTION_ITEMS_BRKDN AS

SELECT PRCHSR_CD

,PGM_NM

,CASE

WHEN 1 <= AI_COMPLETED <= 5 THEN "'1 - 5"

WHEN 6 <= AI_COMPLETED <= 10 THEN "'6 - 10"

WHEN 11 <= AI_COMPLETED <= 14 THEN "'11 - 14"

WHEN 15 <= AI_COMPLETED THEN "15+"

END AS AI_COMP_BKT

,COUNT(DISTINCT MEMBER_KEY_CHAR) AS MBRS_BY_AI_COMP

FROM AI_COMPLETED

GROUP BY

PRCHSR_CD

,PGM_NM

,AI_COMP_BKT

;

QUIT;

Thanks,

saspert

Re: Number Ranges in Excel

NickR — Tue, 16 Aug 2011 16:01:43 GMT

Hi,

In Excel you may add an apostrophe (single quote) at the begining of text - that should fix it. It forces Excel to treat the cell as text instead of a number.

For e.g.

'1-5

'6-10

'11-14

Re: Number Ranges in Excel

saspert — Tue, 16 Aug 2011 21:46:55 GMT

Hi Nick,

I did try the single quote (see my original post). But that is not acceptable by the business because the single quote is also showing up in the report output. I am looking for a better alternative.

Thank you for suggesting though.

-saspert

Re: Number Ranges in Excel

art297 — Tue, 16 Aug 2011 21:59:05 GMT

I can't test it at the moment, but have you tried replacing the hyphen character with an emdash? i.e.,—

You can either copy it from this post or type it by holding your alt button down and typing 8212 using your numeric keypad.

Re: Number Ranges in Excel

Cynthia_sas — Tue, 16 Aug 2011 23:43:59 GMT

Hi:

Excel treats your values as it guesses is best...which isn't always a guess you are happy with. If you can consider using ODS destinations (such as ODS TAGSETS.EXCELXP, for example), you can send in a control instruction that informs Excel to treat the value as TEXT. If you run the attached code, you should see two worksheets: 1) was created from the PROC SQL table and 2) was created from the original dataset. I use a SAS user-defined format for these small lookup lists, because I find it simplifies the code over a long CASE statement or set of IF statements. In either case, the TAGATTR instruction or style override in the PROC REPORT code tells Excel, when it opens the ODS generated file, to treat a particular report column as TEXT (Format:@).

cynthia

proc format;
value aic 1-5 = ' 1 - 5'
6-10 = ' 6 - 10'
11-14 = '11 - 14';
run;

data ai_completed;
infile datalines;
input prchsr_cd $ pgm_nm AI_COMPLETED ;
return;
datalines;
AAA 1 1
AAA 2 1
AAA 3 3
BBB 4 6
BBB 5 8
CCC 6 11
CCC 7 14
run;

proc sql;
create table action_items_brkdn as
SELECT PRCHSR_CD ,PGM_NM, ai_completed,
          put(ai_completed,aic.) as ai_comp_bkt
from ai_completed
group by prchsr_cd, pgm_nm, ai_comp_bkt;
QUIT;

ods tagsets.excelxp file='c:\temp\ai_brkdn.xml' style=sasweb
    options(doc='Help' absolute_column_width='11');

proc report data=action_items_brkdn nowd;
title '1) Using TAGSETS.EXCELXP and TAGATTR';
column prchsr_cd pgm_nm ai_completed ai_comp_bkt;
define prchsr_cd / display;
define pgm_nm / display;
define ai_completed / display;
define ai_comp_bkt / display
         style(column)={tagattr='Format:@'};
run;

proc report data=ai_completed nowd;
title '2) Using Format with Original Dataset';
column prchsr_cd pgm_nm ai_completed;
define prchsr_cd / display;
define pgm_nm / display;
define ai_completed / display 'AI with Format'
         f=aic. style(column)={tagattr='Format:@'};
run;

ods tagsets.excelxp close;

Re: Number Ranges in Excel

saspert — Mon, 22 Aug 2011 16:32:30 GMT

Hi Cynthia,

Thank you for the response. Just wondering if the style(column)={tagattr='Format:@'} works with excel output without using the ExcelXP Tagsets. The particular report which needs this requirement does not use tagsets.

Thanks,
saspert.

Number Ranges in Excel

Cynthia_sas — Wed, 24 Aug 2011 03:17:37 GMT

Hi,

The TAGATTR style override was designed for TAGSETS.EXCELXP. So it is not going to work for any other destination. However, if you created HTML files you could use a similar technique using HTMLSTYLE= style override. There are some tech support notes that talk about HTMLSTYLE.

Cynthia