Entering TEXT(CHAR values) into a numeric variable, but only for needed records without changing the field attributes?!!

[pawan]

Hi All,

Sorry that I always trouble myself in interacting with Excel and come to back to you guys. Here I've another issue and dont know if it is possible to resolve.

I've a regular report sharing to various clients which has got about few numeric variables(values with amounts, so needed to be numeric) and for some reasons I had to generate this as XML file rather than Excel(because excel cant serve my pupose to load data into multible tabs with needed formats like WRAP the grouped observations).

So here I've a certain requirement where we got to block with showing up of amounts for specific observations(records) and thereby my clients are requesting me to put a text like 'BLOCKED' on such numeric fields:smileyshocked:. I never knew that we programmers do need to think to this extent while developing technologies . ...might be we need to think about another new datatype.. Opportunity!!  I doubt if it is really possible but anyways if someone has some ideas pls do share to help me.

Also, it will be greatly appreciated if you suggest me the best way to convert XML files back into Excel files without disturbing the formats like Word WRAP, MERGE, etc..

[data_null__]

Something like perhaps.

proc format;
   value wt 110-high='Blocked' other=[Best7.];
   run;
proc report data=sashelp.class nowindows;
   column _all_;
   define _all_ / display;
   format weight wt.;
   run;

[pawan]

Thanks, But I got to use conditional formatting based on the values of one or more other variables rather than from the values of the same variable on which the format is applied. I think this should be done someway in the COMPUTE BLOCK..

[data_null__]

Yes how about you post some sample data then we don't have to guess what you want.:smileyshocked:

[pawan]

Oops, sorry for the confusion..:smileymischief: Here is an ex. as below
     PROC REPORT DATA=sashelp.shoes;

          COLUMNS _all_;

          DEFINE region / GROUP;

          DEFINE product / GROUP;

          DEFINE sales / DISPLAY FORMAT=dollar10.2;

     RUN;

For the output, I would like to block the amounts of SALES wherever REGION is either 'Africa' or 'Canada' and PRODUCT is 'Slippers', so that SALES value is shown as BLOCKED.

Thanks for your support.

Message was edited by: Pawan Kumar

[data_null__ · Accepted solution]

proc format;
   value blk other='Blocked';
   run;
PROC REPORT DATA=sashelp.shoes;
   COLUMNS _all_;
   DEFINE region / GROUP;
   DEFINE product / GROUP;
   DEFINE sales / DISPLAY FORMAT=dollar10.2;
   compute before region;
      _region=region;
      endcomp;
   compute before product;
      _product=product;
      endcomp;
   compute sales;
      if _region in('Africa' 'Canada') and _product eq: 'Slipper' then call define('sales','format','blk.');
      endcomp;
   RUN;