BookmarkSubscribeRSS Feed
RobW
Calcite | Level 5
Background:
The gist of the report is to display daily counts for a given month. A month-to-date total is easy to do with an RBREAK of course. The challenge is to display a year-to-date summary row, but not show previous months' data in the daily counts. Something like this:

Date Count
10/1 3
10/2 7
10/3 4
---------------------
month 14
to date
---------------------
year 153
to date

so...previous months, like september and so forth, need to be accounted for, but not displayed daily.

Is this possible...summarize everything that is needed but control what is displayed on a row basis??

I bow to the SAS gurus.

rmw
5 REPLIES 5
RobW
Calcite | Level 5
update:

I've tried some CALL DEFINE/style definitions to see if I could collapse (cellheight=0in) the rows if they failed the conditional date logic, but to no avail.
Cynthia_sas
SAS Super FREQ
Hi:
Although you could do this with PROC REPORT, I think that the DATA step is better at controlling what gets written in the manner you describe. PROC REPORT will want to execute a BREAK statement for EVERY break variable group (every month, for example) and while you COULD control that by making a dummy break variable for MTD and YTD break points, you'd have to make a dummy break variable in the DATA step and as long as you were in the DATA step, you may as well write your report there.
The report results from the below program are too lengthy to display, but if you run the program and carefully examine the rows being written, especially, the MTD and YTD rows, you should be able to figure out how to use DATA step IF statements to control what gets written out to the ODS HTML (or RTF or PDF) file. (SASHELP.PRDSALE does not have daily sales -- which is why I showed the product -- so there would be multiple obs in a single month -- they're just for product instead of for each day.)

cynthia
[pre]
** create a subset of data;
proc sort data=sashelp.prdsale out=prdsale;
where year = 1994 and (month between '01JAN94'd and '31OCT94'd)
and country = 'U.S.A.';
by month;
run;

ods listing close;
options nodate nonumber missing = ' ';

ods html file='somebreak.html' style=sasweb;

data _null_;
set prdsale end=eof;
by month;
** read in the data file, set the END= variable;

** housekeeping -- want month name to be a character var;
** and want to retain monthly total and overall total;
length prtmonth $15;
retain alltot montot 0;

** create PRTMONTH character variable;
prtmonth = put(month,monname3.);

** at first obs for every month, initialize monthly total var to 0;
if first.month then montot = 0;

** accumulate overall total and monthly total;
alltot = alltot + actual;
montot = montot + actual;

** display each row in the data;
** only show 4 variables -- but only summing up on ACTUAL;
** the put _ods_ will write out these 4 variables for every data row;
file print ods=(variables=(prtmonth actual predict product));
put _ods_;

** For June and Oct, write out the MTD total just for that month;
** and do NOT write out PREDICT or PRODUCT on the monthly summary line;
if last.month then do;
if prtmonth in ('Jun','Oct') then do;
prtmonth = 'MTD Total';
put @1 prtmonth @2 montot;
end;

** After every month is finished, write out a blank separator line;
put ' ';
end;

** at the end of the file, write out the YTD Total;
if eof then do;
prtmonth = 'YTD Total';
put @1 prtmonth @2 alltot;
end;
format prtmonth $15. montot alltot actual predict dollar14.;
label prtmonth = 'Month';
run;

ods html close;

[/pre]
data_null__
Jade | Level 19
All good points about how much fiddling it would take to get PROC REPORT to suppress the summary rows for all but a few groups. Another alternative is to use the LINE statement to write the summary lines.

While you cannot use the LINE statement in conditional statements, you can use the $VARYING format to write lines with 0 length.

This version of the report does not look as nice as Cynthia's but you may find the technique has some value.

[pre]
options nodate nonumber missing = ' ';
ods html file="%sysfunc(pathname(WORK))\somebreakReport.html" style=sasweb;
proc report data=sashelp.prdsale nowd list out=report;
where year eq 1994 and (month between '01JAN94'd and '31OCT94'd) and country eq 'U.S.A.';
columns month actual predict product;
define month / order order=internal left;
define actual / sum;
define predict / display;
define product / display;
compute before month;
saveMonth=month;
endcomp;
compute after month / style(lines)=[just=center];
if put(savemonth,monname3.) in('Jun','Oct') then do;
text = catx(' ','Actual Sales (MTD):',put(savemonth,monname3.),put(actual.sum,dollar12.2));
l = length(text);
end;
else do;
text = 'Not working';
l = 0;
end;
line text $varying100. l;
endcomp;
compute after / style(lines)=[just=center];
rc = seenum(actual.sum,'ACTUAL.SUM=');
text = catx(' ','Actual Sales (YTD):',put(actual.sum,dollar12.2));
l = length(text);
line text $varying100. l;
endcomp;
run;
ods html close;
[pre]
Cynthia_sas
SAS Super FREQ
Very clever! It did not occur to me to write a line with length of 0. The only issue then, between PROC REPORT and DATA _NULL_ is cosmetic.

With the data _null_ program, the summary line is placed so it's lined in the ACTUAL column. You can line things up that way with the LINE statement in LISTING only. For other ODS destinations, the default line justification would be centered -- the summary numbers will not "line up" under ACTUAL.

Good example!

cynthia
deleted_user
Not applicable
This helps in creating YTD calculation. But, I would like to know how to change this code to work, if there are group variables. like 'montot' breakdown by x,y,z variables. thanks

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1117 views
  • 0 likes
  • 4 in conversation