Most efficient way to format and use a column for counting?

[crawfe]

Hi. I have a large table; the key columns are UpNum (Char) and Date1 (1Jan2019). UpNums are performed continuously.

I need to cumulatively count the No. of UpNums by Month.

I would like to format Date1 to MMYYYY then cumulatively count the instances of UpNum.

(I would like this output to be part of the larger table, since the other columns are just informational).

(I am new to SAS and my thoughts on this were quickly getting too complicated! 🙂  )

Thanks!

Input:                                                   Output:

UpNum         Date1     ...(other cols)   UpNum              Date1_MY       CumCount     ...(other columns)

AA001           4Apr2019                       AA001                 01/2019              1

AA001           5Jan 2019                      AA001                 02/2019              2

AA002           12Dec2018                    AA001                 03/2019              2

AA001           12Feb2019                    AA001                 04/2019              3

AA002           14Feb2019                      ....                         ....                    ....

 AA003           ......                               AA002                 12/2018              1

                                                            AA002                  01/2019             1

                                                            AA002                  02/2019             2    (etc..)

[ballardw]

The assumes that your date variable is actually a SAS date valued variable.

proc freq data=have noprint;
   tables upnum *date1/ out=work.summary (drop=percent);
   format datate  mmyys7.;
run;

Formats will create groups honored by most procedures. The mmyys7. format should display the date as desired. The actual value stored, as displayed with a different format is likely to be the earliest value of the date in a month.

I strongly suggest thinking very carefully about what you will be doing with your data later as having a mix of summary and non-summarized values (the other variables that are not used for the grouping) could cause interesting odd values later.

[Tom]

You can get PROC FREQ to count by formatted value for you, but note that it still keeps one of the real dates as the values.

proc freq data=have;
  tables upnum*date1 / out=counts;
  format date1 yymm7.;
run;

Not sure what other columns you want to include into the summary if they are not unique for the UPNUM*DATE1 combination then which value would you pick?

[crawfe]

Hi. I had done it previously as an exercise by manually creating a small table with two columns,

where I created a column Performed_MY. Then do the cum_count, then do a manual Join back to the original table.

I was hoping to think bigger/more efficient by doing the format and cum_count in the

same program and including all the other informational columns

(too numerous to define individually?).

(my original core code for 2 cols and the calc column; but the table manipulation is manual).

proc freq data=WORK.QUERY_FOR_CUM_BY_PERFORMDT noprint;
table Upnum*Performed_MY/out=temp (drop= percent) sparse ;
run;

data Cum_byPerformDt;
set temp;
by Upnum ;
if first.Upnum then Cum_byPerformDt=0;
Cum_byPerformDt+count;
run;

[PeterClemmensen · Accepted solution]

@crawfe, if I understand your question correctly, the tricky part of your problem is to turn your transactional data into time series data with data points for each month start, even if some month in your time period contains no observations?

If so, then PROC TIMESERIES is a nice tool. Below, i took part of your posted data. This gives you the desired, posted output data

data have;
input UpNum $ date1:date9.;
format date1 date9.;
datalines;
AA001 4Apr2019
AA001 5Jan2019
AA002 12Dec2018
AA001 12Feb2019
AA002 14Feb2019
;

proc freq data=have noprint;
  tables upnum*date1 / out=temp1(drop=percent);
  format date1 mmyys10.;
run;

proc timeseries data=temp1 out=temp2;
   by UpNum;
   id date1 interval=month
               accumulate=total
               setmiss=0;
   var count;
run;

data want(drop=count);
   do until (last.UpNum);
      set temp2;
      by UpNum;
      CumCount+count;
      output;
   end;
   CumCount=0;
run;

[crawfe]

That worked just right. Thanks to you and everyone who commented.

Each UpNum has several fields that are

informational and unique to that UpNum only.

So if I want this calculation to be part of a larger table with those fields, I need to add each other column

by name ( Tables W*X*Y*Z) if I want them included? That's probably best, as was said, to not

accidentally mix column data types.