BookmarkSubscribeRSS Feed
ldeassis
Calcite | Level 5

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;

Proc Report.PNG

8 REPLIES 8
ldeassis
Calcite | Level 5
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
Super User Tom
Super User

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
Calcite | Level 5

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
Super User Tom
Super User

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
Calcite | Level 5

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;

Proc Report2.PNG

ballardw
Super User

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
Calcite | Level 5

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 8 replies
  • 2000 views
  • 0 likes
  • 4 in conversation