My apologies that this is not clearer. It is the values on Prod_Type that is different. For instance, For : Feb, YTD . the Prod_Type value should be 5 but the total is 9 instead. Mar, YTD should be 7 instead of 13 etc. I had put the furniture type next to the numbers to clarify how it should be being added.
So you want prod_type to be a YTD count of distinct PRODUCT values encountered. Then:
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,monthnum,
"Current Month" as Type,
sum(actual) as Actual
from book4
group by country, prodtype, monthnum
order by country, prodtype, monthnum;
quit;
proc sort data=book4 out=book4s equals;
by country prodtype monthnum;
run;
data prod_type_counts (keep=country prodtype monthnum prod_type);
length cum_type_list $300;/*List of distinct products encountered YTD*/
do until (last.prodtype);
set book4s;
by country prodtype monthnum;
if findw(cum_type_list,trim(product),'','E')=0 then do;
cum_type_list=catx(' ',cum_type_list,product);
prod_type=sum(prod_type,1);
end;
if last.monthnum=1 then output;
end;
run;
data want (drop=cum_: monthnum);
do until (last.prodtype);
set cur_month ;
by country prodtype;
output;
end;
do until (last.prodtype);
merge cur_month prod_type_counts;
by country prodtype monthnum;
cum_actual+actual;
type='YTD';
actual=cum_actual;
output;
end;
run;
The prod_type_counts data step is exactly what I need. Thank you! However, the length of cum_type_list in my actual data set can exceed 32767. And it is producing incorrect totals for length of cum_type_list that exceed 32767. Is there an alternative method?
YOu need more the $32767 to hold all the product values? Then you can replace use of the CUM_TYPE_LIST variable with a hash object, which can be check for the presence of each product encountered, and can be dynamically grown by adding each each product's first encounter:
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,monthnum,
"Current Month" as Type,
sum(actual) as Actual
from book4
group by country, prodtype, monthnum
order by country, prodtype, monthnum;
quit;
proc sort data=book4 out=book4s equals;
by country prodtype monthnum;
run;
data prod_type_counts (keep=country prodtype monthnum prod_type);
if 0 then set book4s;
if _n_=1 then do;
declare hash cum_prod_types();
cum_prod_types.definekey('product');
cum_prod_types.definedone();
end;
cum_prod_types.clear();
do until (last.prodtype);
set book4s;
by country prodtype monthnum;
if cum_prod_types.check()^=0 then do;
cum_prod_types.add();
prod_type=sum(prod_type,1);
end;
if last.monthnum=1 then output;
end;
run;
data want (drop=cum_: monthnum);
do until (last.prodtype);
set cur_month ;
by country prodtype;
output;
end;
do until (last.prodtype);
merge cur_month prod_type_counts;
by country prodtype monthnum;
cum_actual+actual;
type='YTD';
actual=cum_actual;
output;
end;
run;
Note that I clear the hash object before starting each prodtype. So none of the cumulative counts cross PRODTYPEs.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.