The SAS Output Delivery System and reporting techniques

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

Accepted Solution Solved
Reply
Contributor
Posts: 37
Accepted Solution

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


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 Smiley Wink. ...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..


Accepted Solutions
Solution
‎10-11-2013 09:01 AM
Respected Advisor
Posts: 3,776

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

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;

View solution in original post


All Replies
Respected Advisor
Posts: 3,776

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

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;
Contributor
Posts: 37

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

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..

Respected Advisor
Posts: 3,776

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

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

Contributor
Posts: 37

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

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

Solution
‎10-11-2013 09:01 AM
Respected Advisor
Posts: 3,776

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

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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 653 views
  • 0 likes
  • 2 in conversation