SAS PROC REPORT Conditional Output in a Column

[amit7urmc]

I am trying to learn SAS and specifically PROC REPORT. I am using SASHELP.CARS dataset.

What I want to achieve in the 6th column of the output, labelled as 'Number of Cars > Mean(Invoice)' to compute number of cars whose Invoice is greater than the Group's mean of Invoice. I am using the code below.

PROC REPORT DATA=sashelp.CARS NOWD OUT=learning.MyFirstReport;
COLUMNS Type Origin INVOICE=Max_INVOICE INVOICE=Mean_Invoice 
INVOICE=Count_Invoice TEST DriveTrain;
DEFINE Type / Group 'Type of Car' CENTER;
DEFINE Origin / Group 'Origin of Car' CENTER;
DEFINE Max_Invoice / ANALYSIS MAX 'Max of Invoice';
DEFINE Mean_Invoice / ANALYSIS MEAN 'Mean of Invoice';
DEFINE Count_Invoice / ANALYSIS N FORMAT=5.0 'Total Number of Cars' center;
DEFINE DriveTrain / ACROSS 'Type of DriveTrain of Car';
DEFINE TEST / COMPUTED 'Number of Cars > Mean(Invoice)' center;
COMPUTE TEST;
     TEST=N(_c7_>Mean_Invoice);
ENDCOMP;
RUN;

The Output that I am getting is in the image below.

I don't think that is the correct output since all the rows in the column show a value of 1. How do I get the desired output in the 6th column of the output?

[Cynthia_sas]

Hi:
Why are you referencing _c7_ ? And given the left-to-right rule of PROC REPORT, you can't use _c7_ to compute anything in the TEST column. Remember that PROC REPORT puts every column down on the report working from left to right. So at the point in time when PROC REPORT needs to calculate a value for TEST it has not yet placed anything down in absolute column 7.

Also, you are creating a grouped report, so it is nearly impossible for the number of cars that are greater than the mean invoice to be calculated from summary numbers.

Just some observations on your code. I don't think it's going to do what you want.

Cynthia

[amit7urmc]

@Cynthia_sas

Hi!

I realised my mistake. I thought that _c7_ is a way to refer to the 7th column in the Original (incoming) dataset but I waswrong. _c7_, I think is the 7th column in the output of PROC REPORT. Thank you for correcting me.

It is sad that such a value can't be computed. It would be nice to know for each Type and Origin combination, how many cars' invoice was over that specific group combination's Mean(Invoice). I thought there is an easy way to get that but looks like I was wrong on this one too ..

[Cynthia_sas · Accepted solution]

Hi, there is an easy way to get this number, but it requires that EVERY row in SASHELP.CARS is  compared to the overall invoice mean. You can do this but you need a little help from other procedures....probably PROC SQL or PROC MEANS. I prefer PROC MEANS, since I avoid remerging statistics back, which is what SQL would do. I prefer to be in control of the MERGE:

And here's the output:

Cynthia

[VDD]

Thank you Cynthia for the example. 

[amit7urmc]

@Cynthia_sas

Thank you very much. I have been staring at the screen for some time now, in search of a solution. Really appreciate. 

[amit7urmc]

I had asked the same question on Stackoverflow as well. I saw that reply today. I guess in both the places, the initial logic of adding a flag column is there (if I am not wrong). I am writing that link for cross reference if somebody needs it. https://stackoverflow.com/questions/52351214/sas-proc-report-conditional-output-in-a-column/52382463... 

That particular code produces output as below.