proc freq...would like to add a column as a data check

[DONBSR]

We bring in 7 daily MXG PDBs and read RMFINTRV to check that we have 96 intervals each day before we build a Weekly PDB. We use PROC FREQ to check that. We'd like to add 1 column to the current output...SYSTEM. Just to verify that we have the correct data. I have tried performing the procedure using a "BY SYSTEM" which give sme a "TITLE" at the top of the report with the value for SYSTEM. And when I test it with data from another LPAR it does abend with a message...ERROR: DATA SET WORK.R1 IS NOT SORTED IN ASCENDING SEQUENCE. THE CURRENT BY GROUP HAS SYSTEM*ID = MVSX AND THE NEXT BY GROUP HAS SYSTEM*ID = MVSM...so in a way that does give me the information that one of the input files is incorrect.

DATA RMFINTRV ;
SET MON.RMFINTRV
TUE.RMFINTRV
WED.RMFINTRV
THU.RMFINTRV
FRI.RMFINTRV
SAT.RMFINTRV
SUN.RMFINTRV
;

DATA R1;
SET RMFINTRV;
KEEP DATE;
IF WEEKDAY(DATE)=5 AND HOUR=12 THEN
CALL SYMPUT('MEANDATE',PUT(DATE,DATE.));

PROC FREQ ORDER=DATA DATA=R1;
TABLES DATE / OUT=R2;

THE FREQ PROCEDURE

DATE*OF*STARTIME

CUMULATIVE CUMULATIVE
DATE FREQUENCY PERCENT FREQUENCY PERCENT
--------------------------------------------------------------
05DEC2016 96 14.29 96 14.29
06DEC2016 96 14.29 192 28.57
07DEC2016 96 14.29 288 42.86
08DEC2016 96 14.29 384 57.14
09DEC2016 96 14.29 480 71.43
10DEC2016 96 14.29 576 85.71
11DEC2016 96 14.29 672 100.00

This is what we would like the output to be

                                    CUMULATIVE CUMULATIVE
DATE      SYSTEM  FREQUENCY PERCENT FREQUENCY  PERCENT
--------------------------------------------------------------
05DEC2016  MVSM      96       14.29    96       14.29
06DEC2016  MVSM      96       14.29    192      28.57
07DEC2016  MVSX      96       14.29    288      42.86
08DEC2016  MVSM      96       14.29    384      57.14
09DEC2016  MVSM      96       14.29    480      71.43
10DEC2016  MVSM      96       14.29    576      85.71
11DEC2016  MVSM      96       14.29    672     100.00

[ballardw]

It is a little hard to see what you may be attempting as you do not post code with a By statment.

In general any SAS procedure that supports by group processing will require the data set to be sorted by the By variables if you want one set of out put for each value level OR the use of the notsorted option to process in the order they appear within the data set.

If you just want a count you may want a different procedure. I am guessing here:

proc tabulate data=data;
   class subject;
   class date;
   table subject,
          date,
          n='Count';
run;

with this structure you get one table for each value of subject and a count of how many times each date appears for each level of subject in the data set.  The table will have line like: Subject ABC at the top to identify each one.

Alternatively you could use

proc tabulate data=data;
   class subject;
   class date;
   table 
          date,
          subject* n='Count';
run;

to create on column of counts for each subject

[DONBSR]

The notsorted option allows the job to complete without abending which in my mind is fine....but my charge was to find a way to include that system value in the saslist. I will try the tabulate but I'm pretty sure that what is wanted is not possible with either FREQ or Tabulate. Thank you for replying....

[Astounding · Accepted solution]

You should be able to get the proper table with:

proc freq data=r1;

tables date * system / missing list out=r2;

run;

You don't need ORDER=DATA to get the dates to be in order.  The default action of PROC FREQ to order the column values lowest to highest value.  That order would match your ORDER=DATA as long as Monday is the earliest batch of data. 

You don't need a BY statement to get this table.  To debug the error message, you would have to post the code that contains the BY statement.