BookmarkSubscribeRSS Feed
christa
Calcite | Level 5


I am using special missing values to differentiate between different "nothings" in a numeric column.

Is there a way to get these to excel via ods tagsets.excelxp?

9 REPLIES 9
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Could you clarify with an example?  Your proc report within the ods tageset open/close should print all text out, however its not a given that what Excel displays is the same as witihin SAS. 

christa
Calcite | Level 5

the problem is that it is not text, but numeric.

and all missing values, wether normal or special, show in excel as .

in SAS i am fine.

I will post an example tomorrow.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Perhaps you could convert the column to character prior to exporting to Excel?  Will wait to see example.

options missing='$';
data have;
  attrib test format=8.;
  test=12;
  output;
  test=.;
  output;
run;
data want (drop=test);
  set have;
  attrib text_for_export format=$10.;
  test_for_export=strip(put(test,best.));
run;
proc export...;

christa
Calcite | Level 5


Here is my example (how can i post it nicely?)


RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

Sorry, not sure what your example is trying to do.  I have changed your code slightly to get it working, however the output from the tabulate looks pretty much identical to the input dataset other then three new variables _TYPE_, _PAGE_, _TABLE.  Also I note that the proc tabulate itself is not honoring the missing value per the dataset as you have no indicator - only for the datastep.  So try using "options missing=B" at the start of your program.

This code works and shows 'B' in the output, it does this by putting the value into a character variable and using that.  (Note you can use the out= from the proc tabulate, and then post process that before outputting to Excel).

options missing=B;
data test;
  missing B;
  input v_id $ v_value;
datalines;
1 1
2 0
3 .B
4 0
5 -1
6 55
8 4003
;
run;

data inter;
  set test;
    attrib my_val format=$10.;
    my_val=strip(put(v_value,best.));
run;

ods _all_ close;
ods tagsets.excelxp file="S:\Temp\Rob\test.xls" style=statistical options (frozen_headers="1" absolute_column_width="none" zoom="80");
ods tagsets.excelxp options (sheet_name="Test");
proc report data=work.inter nowd split="~"
            style(header)=[background = #DBE5F1 foreground = #000099 font_weight = bold ]
            style(column)=[just=l borderrightcolor=white borderleftcolor=white
                           borderleftwidth=1 bordertopcolor=white borderbottomcolor=white
                           borderrightwidth=1 bordertopwidth=1 borderbottomwidth=1];
  columns v_id my_val;
  define v_id / "Id";
  define my_val / "Value";
run;
ods tagsets.excelxp close;

christa
Calcite | Level 5

Sorry if I did not show exactly where my problem is. I need a numeric field in excel in a specific format. To get this, the xml written by SAS needs to show "real number" in most places, but specific Characters (such as . , X, B) in others.

I hoped to achieve this with special missing values, but cant get it working.

P.S. I just missed this line while copying my code:

missing=B;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

I am afraid I don't know of a direct way of doing that.  The specific missing character functionality you are talking about is a SAS specific item, Excel does not have that as missing is just missing.  So from before, you would need to convert it character for Excel to show characters.  The only other options I can think of is:

- Change your actual missings to a specific number which can't happen, e.g. -999999.

- Post process the XML file which you generated, so read it in as a text file and search for:

<Cell ss:StyleID="data__l1" ss:Index="1"><Data ss:Type="Number"> xx </Data></Cell>

Then write your specific character in where the xx is.

- Alternatively use Excel to replace blanks after export.

Peter_C
Rhodochrosite | Level 12

you might have success using traffic lighting to set the background formatting for distinctive missing values

and remembering that the excel custom format allows separate custom formats (separated by semi-colons) for positive negative zero and text values.

However this approach wouldn't distinguish between different special missing values

Andre
Obsidian | Level 7

Lateral answer for your  "nicely"   question

My trick is to copy the sas colored code from the pgm file to a new temporary word file without too much space gap between lines

then in a second step copy again the colored code from word to here

AW

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 2464 views
  • 7 likes
  • 4 in conversation