What I wanted to point at: BOTH codes create a character variable. Since your initial problem seems to be that you have a dataset where encrypted_id is numeric, this dataset CANNOT come from one of the codes you posted. It may even be that encrypted_id does not exist in this dataset, and is created by SAS on-the-fly, which would make it numeric (per default). In that case you would also most likely get an "uninitialized" NOTE.   Please post the log of the whole step that results in the message ERROR 29-185: Width specified for format HEX is invalid.       ... View more

First question: How are you bringing the data from Excel into SAS?   Assuming your answer to that question is either "Proc Import" or an import wizard then expect continuing problems from varying variable types, lengths and possibly even variable names.   Both of those methods invoke algorithms to guess the content of a file and often very few records of the file. So if the first 20 rows of data for a variable only contain 4 characters then that could be the length set for a field and the remainder of the data, i.e. below the 20 rows, is truncated to that. If this is to be an ongoing process with multiple files of the same layout then you might want to address the reading the data issue now before you start writing lots of fixit code. In the long run, saving data that starts as Excel to CSV and then using  DATA step code to read a consistent layout is much more consistent and repeatable.   It isn't clear exactly what change you mad by "expanded the list of revenue codes" or even which table in the syntax you show would represent that file. Best would be to provide two small input sets, indicating which one is similar to which in the syntax you show (we are not going to have your libraries mapped for instance) as data step code showing just enough variables and values to demonstrate what you have and then an example of the desired result using those two input sets. ... View more

@Wolverine wrote: It's frustrating because only about 1000 of the 28000 codes contain letters. If it wasn't for those 1000 codes, I could use ranges and it would be a lot easier. Another idea would be to recode into a new variable so that multiple zeroes would replace the letters. The new variable could be numeric, and ranges would work because the multiple zeroes would push the alphanumeric codes out of range. From that point, it would still take some work to convert those codes back to their original form, but at least I'd only have 1000 to deal with instead of 28,000. Is every single code exactly 5 characters? Then possibly that is a true statement. I agree with @Reeza about ways to build lists that will work with proc format. One of the things I routinely do is convert documentation of "acceptable values" for data fields that are supplied for a project and use Excel to help write format strings, labels and sometimes input  and informat statements.   Wait until you get to work with ICD-10 codes where the lengths vary a lot as they keep adding "decimal point values" to indicate submembership: foot=> toe> joint on toe and so on. ... View more

I am giving some clues, You can follow this : data have; input adj_code year value treatment $ state $; datalines; 0 2005 12 therap MI 1 2006 20 inpati CA 2 2007 22 inpati MI 1 2005 21 inpati MI 2 2005 22 therap CA 0 2005 10 therap CA 1 2006 19 inpati CA 2 2007 10 inpati MI 2 2006 20 therap CA 1 2006 11 inpati MI 0 2005 90 therap CA 0 2006 11 inpati MI 0 2005 99 therap MI ; run; proc format; value adj_code 0="0-NO ADJUSTMENT OF CLAIMS WAS REQUIRED" 1="1-AT LEAST ONE ORIGINAL CLAIM AND AT LEAST ONE ADJUSTMENT CLAIM" 2="2-NOT POSSIBLE TO COMPLETE THE ADJUSTMENT PROCESS"; value $treatment 'therap'="OTHER THERAPY PERCENTAGES" 'inpati'="INPATIENT PERCENTAGES"; run; ods escapechar='^'; ods pdf; proc report nowd data=have spanrows; title1 "Adjustment code claims 2.2.2, 2.3.2"; title2 "&statename., 0-18 years old"; title3 "2005-2007"; column state (adj_code) treatment,year,value;* year value treatment; define state/group; break after state/page; define adj_code/'Adjustment Code' group format=adj_code.; define treatment/''across order=internal format=$treatment.; define year/' 'across order=internal ; define value/'' sum; rbreak after/ summarize; run; ods pdf close; ... View more

I did some more investigating, and I discovered that Proc Report is better suited to my needs. In fact, I can use Proc Report to generate the column totals before I even dump the output into Excel.   I also found that many people have trouble with Excel cell naming conventions (ie, A1, A2, etc) than absolute reference (RC[-1]).   So here is my updated syntax.  It works, except that I need to move the 200x_COUNT total to the bottom of the 200x_PERCENTAGE column.  That's what the "formula:RC[-5]" part is intended to do -- copy the cell from 5 columns over.  But it's not working.   proc report data=UT.OT_UT_FREQ_MERGE spanrows; title "PRCDR Proc Report"; COLUMN PRCDR_CD_SYS UT2005_COUNT UT2006_COUNT UT2007_COUNT UT2008_COUNT UT2009_COUNT UT2010_COUNT UT2005_PERCENT UT2006_PERCENT UT2007_PERCENT UT2008_PERCENT UT2009_PERCENT UT2010_PERCENT; define PRCDR_CD_SYS / group; define UT2005_COUNT / group DISPLAY ANALYSIS SUM; define UT2006_COUNT / group DISPLAY ANALYSIS SUM; define UT2007_COUNT / group DISPLAY ANALYSIS SUM; define UT2008_COUNT / group DISPLAY ANALYSIS SUM; define UT2009_COUNT / group DISPLAY ANALYSIS SUM; define UT2010_COUNT / group DISPLAY ANALYSIS SUM; define UT2005_PERCENT / group style(column)={tagattr='format:0.00%'}; define UT2006_PERCENT / group style(column)={tagattr='format:0.00%'}; define UT2007_PERCENT / group style(column)={tagattr='format:0.00%'}; define UT2008_PERCENT / group style(column)={tagattr='format:0.00%'}; define UT2009_PERCENT / group style(column)={tagattr='format:0.00%'}; define UT2010_PERCENT / group style(column)={tagattr='format:0.00%'}; compute after; PRCDR_CD_SYS="Column N"; endcomp; compute UT2005_PERCENT; if upcase(_break_)='_RBREAK_' then do; call define(_col_,"style","style={tagattr=""formula:RC[-5]""}"); end; endcomp; rbreak after / summarize; RUN; ... View more

Check your log for a warning. That should tell you something's wrong. Your code doesn't make a lot of sense as you select variables multiple times, but don't rename them, how would you know which dataset they come from? You can look into the coalesce function, which may help. ... View more