Thank you for setting up the DATA step for this problem. As @ballardw said, your program orders the months alphabetically instead of in calendar order. So I modified your data step to add a MONTHNUM variable, which is then added to the group by and sort by clauses.
data WORK.BOOK4;
infile datalines dsd truncover;
input ACTUAL:DOLLAR20.2 COUNTRY:$6. PRODTYPE:$9. PRODUCT:$20. YEAR:F12. MONTH:$3.;
monthnum=findw('Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec',month,' ','e');
datalines4;
$107.00,CANADA,FURNITURE,SOFA,1994,Jan
$354.00,CANADA,FURNITURE,SOFA,1994,Feb
$101.00,CANADA,FURNITURE,OTTOMAN,1994,Mar
$553.00,CANADA,FURNITURE,SOFA,1994,Apr
$877.00,CANADA,FURNITURE,SOFA,1994,May
$431.00,CANADA,FURNITURE,SOFA,1994,Jun
$511.00,CANADA,FURNITURE,CHAIR,1994,Jul
$157.00,CANADA,FURNITURE,STOOL,1994,Aug
$520.00,CANADA,FURNITURE,SOFA,1994,Sep
$114.00,CANADA,FURNITURE,SOFA,1994,Oct
$277.00,CANADA,FURNITURE,SOFA,1994,Nov
$561.00,CANADA,FURNITURE,CUPBOARD,1994,Dec
$267.00,CANADA,FURNITURE,BED,1994,Jan
$347.00,CANADA,FURNITURE,BED,1994,Feb
$991.00,CANADA,FURNITURE,BED,1994,Mar
$923.00,CANADA,FURNITURE,BED,1994,Apr
$437.00,CANADA,FURNITURE,DESK,1994,May
$737.00,CANADA,FURNITURE,BED,1994,Jun
$104.00,CANADA,FURNITURE,BED,1994,Jul
$840.00,CANADA,FURNITURE,BED,1994,Aug
$704.00,CANADA,FURNITURE,BED,1994,Sep
$889.00,CANADA,FURNITURE,BED,1994,Oct
$107.00,CANADA,FURNITURE,LOVESEAT,1994,Nov
$571.00,CANADA,FURNITURE,BED,1994,Dec
$355.00,CANADA,FURNITURE,TABLE,1994,Jan
$506.00,CANADA,FURNITURE,TABLE,1994,Feb
$585.00,CANADA,FURNITURE,DRESSER,1994,Mar
$634.00,CANADA,FURNITURE,TABLE,1994,Apr
$662.00,CANADA,FURNITURE,TABLE,1994,May
$783.00,CANADA,FURNITURE,TABLE,1994,Jun
$786.00,CANADA,FURNITURE,TABLE,1994,Jul
$710.00,CANADA,FURNITURE,CHAIR,1994,Aug
$950.00,CANADA,FURNITURE,TABLE,1994,Sep
$274.00,CANADA,FURNITURE,SOFA,1994,Oct
$406.00,CANADA,FURNITURE,TABLE,1994,Nov
$515.00,CANADA,FURNITURE,TABLE,1994,Dec
$877.00,CANADA,FURNITURE,BED,1994,Jan
$845.00,CANADA,FURNITURE,CHAIR,1994,Feb
$425.00,CANADA,FURNITURE,CHAIR,1994,Mar
$899.00,CANADA,FURNITURE,BENCH,1994,Apr
$987.00,CANADA,FURNITURE,DESK,1994,May
$641.00,CANADA,FURNITURE,BED,1994,Jun
$448.00,CANADA,FURNITURE,CHAIR,1994,Jul
$341.00,CANADA,FURNITURE,CHAIR,1994,Aug
$137.00,CANADA,FURNITURE,CHAIR,1994,Sep
$235.00,CANADA,FURNITURE,CHAIR,1994,Oct
$482.00,CANADA,FURNITURE,CHAIR,1994,Nov
$678.00,CANADA,FURNITURE,CHAIR,1994,Dec
$942.00,CANADA,FURNITURE,DESK,1994,Jan
$912.00,CANADA,FURNITURE,DESK,1994,Feb
$768.00,CANADA,FURNITURE,CHAIR,1994,Mar
$951.00,CANADA,FURNITURE,DESK,1994,Apr
$768.00,CANADA,FURNITURE,NIGHTSTAND,1994,May
$978.00,CANADA,FURNITURE,DESK,1994,Jun
$20.00,CANADA,FURNITURE,DESK,1994,Jul
$298.00,CANADA,FURNITURE,DESK,1994,Aug
$193.00,CANADA,FURNITURE,TABLE,1994,Sep
$336.00,CANADA,FURNITURE,DESK,1994,Oct
$617.00,CANADA,FURNITURE,DESK,1994,Nov
$709.00,CANADA,FURNITURE,SOFA,1994,Dec
;;;;
proc sql;
create table cur_month as
select
Country,
prodtype,
Month,
"Current Month" as Type,
sum(actual) as Actual,
count(distinct product) as Prod_Type
from book4
group by country, prodtype, monthnum, month
order by country, prodtype, monthnum, month;
quit;
Then, to follow the twelve months of current values, by the same twelve months year with YTD values, you can read the CUR_MONTH data set twice, as in:
data want (drop=cum_:);
do until (last.prodtype);
set cur_month ;
by country prodtype;
output;
end;
do until (last.prodtype);
set cur_month ;
by country prodtype;
cum_actual+actual;
cum_pt+prod_type;
type='YTD';
actual=cum_actual;
prod_type=cum_pt;
output;
end;
run;
Now, if you have multiple years, and your want to do this for each year, you need to add YEAR to the group by and sort by clauses, between PRODTYPE and MONTHNUM. And you would have to change the data step to use
do until (last.year);
and
by country prodtype year;
Click image to register for webinar
