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
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
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
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.
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;
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: