Changing column values based on the values in another column.

[ldeassis]

Hello,

I'm trying to assign values to a column based on logic that uses the values in another column.  I'm using Proc Report, and the logic I'd like to create is something like this: if (Status eq 'WORKED' and PCT_Worked eq 0) then 'N/A' else if Status eq 'AUTOCLSD' then ''.  I've added an extra column to my report to show what the final result should look like (green header). The code is below. Thank you.

ODS LISTING CLOSE;
ods excel file= "/sas/sasperm18_prod/Fraud/DDA/temp/&FileName" options(sheet_name="SevData") ;;
options missing= 0;
PROC REPORT DATA=Reports.temp_Mon_Final2 NOWD HEADLINE HEADSKIP
contents = "Mon-Alerts by Severity"
style(header) =
 [just = c
 font_face = arial
 font_size = 4
 foreground = white
 background = gray
 protectspecialchars=off] ;
 title "Mon Alerts by Severity";
 column Severity WORKED_SORT WORKED_STATUS Status (CREATE_YM,(Worked)) PCT_WORKED PCT_WORKED=PCT_WKD;
 define Severity / group style(column)= header 'Severity' style={just=l vjust =b } order=internal descending ;
 define WORKED_SORT / group style(column)= header 'Sorting' style={just=l vjust =b } NOPRINT;
 define WORKED_STATUS  / group style(column)= header 'WStatus' style={just=l vjust =b } noprint ;
 define Status / group style(column)= header 'Status' style={just=l vjust =b } ;
 define CREATE_YM/ across order=internal '' style={just=l vjust =b };
 define Worked/ analysis sum '' format=15. style={just=c vjust =b } ;
 define PCT_WORKED/ style(column)= header '% Worked' format=5.2 style={just=l vjust =b } missing;
rbreak after /summarize; 
 compute after ;
 Severity="Total" ;
 PCT_WORKED.SUM = .; 
 endcomp;
 run;
%runquit;
ods excel close;
%runquit;

[acordes]

take a look at this

https://communities.sas.com/t5/New-SAS-User/Proc-Report-compute-if-then/td-p/651148 

[ldeassis]

Acords,

Thank you for the information. I had seen that before, but I haven't been able to translate that "formatting" logic into something I can use to change specific values in one column based on another. Thanks

[Tom]

As long as the column you want to reference is to the left of the column you want to modify you should be able to test the value in your compute block.  You can always include the referenced column earlier if needed and just make it a hidden column.

[ldeassis]

Tom,

I should have mentioned that I very new to SAS.  Yes, the  Status column is at the left of the PCT_Worked column as shown in the attached screen print.  I'm not sure how to create the compute column. I've tried the following, but got errors. Thanks

COMPUTE PCT_WKD;
 if PCT_WORKED EQ 0 then do;
      if Status =:'WORKED' THEN 'N/A';
      end;
     else if Status =:'AUTOCLSD' THEN '';
      end;
ENDCOMP;

180-322: Statement is not valid or it is used out of proper order.

ERROR 161-185: No matching DO/SELECT statement.

---

[Tom]

You need to have a full STATEMENT after a THEN.  You probably want an ASSIGNMENT statement there.

Example:

proc report data=sashelp.class ;
  column name sex new ;
  define new / computed ;
  compute new / char length=20;
    if sex='M' then new='Male';
    else new='Female';
  endcomp;
run;

[ldeassis]

Tom,

Appreciate your reply and example. I was able to create another column that had the values I want to use to replace zeros with in the PCT_WORKED column. Not sure if I've been clear, but what I need is to find a way to replace "zeros" in an existing column (PCT_WORKED) using logic from the Status column. The code below only gives me a new column with "blank" values.  Here's the code and a screen print. Thank you for help.

column Severity WORKED_SORT WORKED_STATUS Status (CREATE_YM,(Worked)) PCT_WORKED NEW;
 define Severity / group style(column)= header 'Severity' style={just=l vjust =b } order=internal descending ;
 define WORKED_SORT / group style(column)= header 'Sorting' style={just=l vjust =b } NOPRINT;
 define WORKED_STATUS  / group style(column)= header 'WStatus' style={just=l vjust =b } noprint ;
 define Status / group style(column)= header 'Status' style={just=l vjust =b } ;
 define CREATE_YM/ across order=internal '' style={just=l vjust =b };
 define Worked/ analysis sum '' format=15. style={just=c vjust =b } ;
 define PCT_WORKED/ style(column)= header '% Worked' format=5.2 style={just=l vjust =b } missing;
 define NEW / computed;
 COMPUTE NEW / char length=10;
        if (Status = 'WORKED' and PCT_WORKED = '0') THEN NEW = 'N/A';
         else if Status = 'AUTOCLSD' THEN NEW = '' ;
 ENDCOMP;

[ballardw]

Look at these bits:

 define PCT_WORKED/ style(column)= header '% Worked' format=5.2 style={just=l vjust =b } missing;
 define NEW / computed;
 COMPUTE NEW / char length=10;
        if (Status = 'WORKED' and PCT_WORKED = '0') THEN NEW = 'N/A';

Pct_worked is numeric, but in the compute block you compare to a character value. Data steps may attempt to convert to the proper type but I am not sure that Proc Report will attempt that. Did the LOG show any note about incorrect data types?

I would suggest removing the quotes around the 0  in the comparison.

[ldeassis]

Hello,

I did change the code per your suggestion, but still got a "blank" column for the New column. It looks like there's no way to replace the values in an exiting column, because the compute will be a new column; which is fine if I could get the results to show correctly in the column. That way I could simply hide the PCT_Worked column. Thanks for your suggestion. 

define NEW / computed;
 COMPUTE NEW / char length=10;
        if (Status = 'WORKED' and PCT_WORKED = 0) THEN NEW = 'N/A';
         else if Status = 'AUTOCLSD' THEN NEW = '' ;
 ENDCOMP;