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:
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+'
;
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.
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;
yes I need for august and the code provided errors out with "ERROR: Missing LABEL variable."
@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.
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.
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;
You should have a WARNING that the format "benefit" is not preloadable.
Look at the documentation lots of good infor there.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.