topic PROC REPORT COMPLETECOLS NOT SHOWING ALL DATES AS ACROSS COLUMN in SAS Programming

PROC REPORT COMPLETECOLS NOT SHOWING ALL DATES AS ACROSS COLUMN

Ninja_turtle — Fri, 18 Aug 2023 17:35:19 GMT

Hi All,

I have a proc report where I am trying to get all the dates as across column where few of the dates data is missing but it is not working.

You can see the "06AUG2023" column is missing. We don't have the data for that day but using preloadfmt and completecols it should have worked. Any guess what's the issue?

Code:

Proc Format;

Value $types

'Overdue ' = 'Overdue '

'Due Today (0-2) ' = 'Due Today (0-2) '

'Due 3-5 Days '= 'Due 3-5 Days '

'Due 6-10 Days '= 'Due 6-10 Days '

'Due Over 10 Days'= 'Due Over 10 Days'

;

Run;

proc format ;

value benefit

'01Aug2023'd-high = [date9.]

;

run;

PROC REPORT DATA=UM_SPLIT2 NOWD spanrows SPLIT="*" completecols completerows missing;

where type='Incoming' and um_unit='BH';

title1;

format MPODAYS $types.;

column

UM_UNIT

MPODAYS

ttl

,Report_Date ;

Define Report_Date / across " " order=internal preloadfmt format=benefit.;

define UM_UNIT / group " " order=internal ;

define ttl / sum " " style=[tagattr = 'format:#,##0'];

DEFINE MPODAYS / group "Summary by Due Date and by Member/Provider"

order=internal preloadfmt ;

break after UM_UNIT / summarize STYLE = BREAK;

compute after UM_UNIT;

line '';

endcomp;

RUN;

Proc report completecols not working

Ninja_turtle — Fri, 18 Aug 2023 17:42:50 GMT

Hi All,

I have a proc report where I am trying to get all the dates as across column where few of the dates data is missing but it is not working.

You can see the "06AUG2023" column is missing. We don't have the data for that day but using preloadfmt and completecols it should have worked. Any guess what's the issue?

Code:

Proc Format;
Value $types
'Overdue ' = 'Overdue '
'Due Today (0-2) ' = 'Due Today (0-2) '
'Due 3-5 Days '= 'Due 3-5 Days '
'Due 6-10 Days '= 'Due 6-10 Days '
'Due Over 10 Days'= 'Due Over 10 Days'
;
Run;

proc format ;
value benefit
'01Aug2023'd-high = [date9.]
;
run;

PROC REPORT DATA=UM_SPLIT2 NOWD spanrows SPLIT="*" completecols completerows missing;
where type='Incoming' and um_unit='BH';
title1;
format MPODAYS $types.;

column
UM_UNIT
MPODAYS
ttl
,Report_Date ;

Define Report_Date / across " " order=internal preloadfmt format=benefit.;
define UM_UNIT / group " " order=internal ;
define ttl / sum " " style=[tagattr = 'format:#,##0'];
DEFINE MPODAYS / group "Summary by Due Date and by Member/Provider"
order=internal preloadfmt ;

break after UM_UNIT / summarize STYLE = BREAK;
compute after UM_UNIT;
line '';
endcomp;
RUN;

Re: PROC REPORT COMPLETECOLS NOT SHOWING ALL DATES AS ACROSS COLUMN

Tom — Fri, 18 Aug 2023 17:44:05 GMT

Not sure what you think it is going to preload for that BENEFIT format.

Do you want a separate column for every day from the start of August to the end of time?

Try it with a simple format definition.

value benefit 
'01Aug2023'd-high = '01Aug2023+'
;

Re: PROC REPORT COMPLETECOLS NOT SHOWING ALL DATES AS ACROSS COLUMN

Ninja_turtle — Fri, 18 Aug 2023 17:49:53 GMT

Hi Tom,

Thank you for the suggestion but I am trying to create the category so that everyday has a column like in my screenshot you won't find 6thAugust column which I want with zero as value or . to show that we don't have data for that day.'

The read arrow shows that 6th August data is not there hence no column I want the column for those days as well where we don't have data.

Re: PROC REPORT COMPLETECOLS NOT SHOWING ALL DATES AS ACROSS COLUMN

Tom — Fri, 18 Aug 2023 17:54:18 GMT

So you need a record for AUG 6th in your format definition.

You still haven't stated what time window you want.

Perhaps the month of August?

data formats;
 fmtname='benefit';
 do start='01AUG2023'd to '31AUG2023'd ;
   value=put(start,date9.);
   output;
  end;
run;
proc format cntlin=formats;
run;

Re: PROC REPORT COMPLETECOLS NOT SHOWING ALL DATES AS ACROSS COLUMN

Ninja_turtle — Fri, 18 Aug 2023 17:56:57 GMT

yes I need for august and the code provided errors out with "ERROR: Missing LABEL variable."

Re: PROC REPORT COMPLETECOLS NOT SHOWING ALL DATES AS ACROSS COLUMN

Tom — Fri, 18 Aug 2023 17:57:43 GMT

@Ninja_turtle wrote:

yes I need for august and the code provided errors out with "ERROR: Missing LABEL variable."

So fix it to use LABEL instead of VALUE.

Re: PROC REPORT COMPLETECOLS NOT SHOWING ALL DATES AS ACROSS COLUMN

ballardw — Fri, 18 Aug 2023 18:24:08 GMT

You will need to restrict the range of your variable to allow use of Preloadfmt.

Example:

459  proc format;
460  value dummy
461    11-high=[Z5.]
462    other=' '
463  ;
NOTE: Format DUMMY is already on the library WORK.FORMATS.
NOTE: Format DUMMY has been output.
464  run;

NOTE: PROCEDURE FORMAT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


465
466  proc report data=sashelp.class;
467     columns name age,weight;
468     define name/group;
469     define age/across order=data preloadfmt format=dummy.;
470  run;

WARNING: The format for variable Age cannot be preloaded. A finite set of
         formatted values cannot be produced from the format.  The format
         is not recognized, is a SAS format, calls a function, or contains
         a nested format in its definition. Preload will have no effect.
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: PROCEDURE REPORT used (Total process time):
      real time           0.02 seconds
      cpu time            0.03 seconds

Preloadfmt usage wants a discrete list of values no numeric ranges even short ones. Remember that 0-1 interval contains an infinite number of values. It also not fond of the use [someotherformat.] intended for ranges.

The keyword Other does work but the remaining values pretty much require value='display' pairs for use with

Preloadfmt.

Re: Proc report completecols not working

data_null__ — Fri, 18 Aug 2023 20:52:22 GMT

You should have a WARNING that the format "benefit" is not preloadable.

Look at the documentation lots of good infor there.