Solved: Re: Distinct Cumulative totals year to date - Page 2

NewSASPerson · Posted 11-19-2019 01:58 PM

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.

mkeintz · Posted 11-19-2019 03:32 PM

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 hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

NewSASPerson · Posted 11-19-2019 09:30 PM

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?

mkeintz · Posted 11-19-2019 10:32 PM

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

NewSASPerson · Posted 11-20-2019 09:23 AM

Thank you very much! This is exactly what I needed.

Re: Distinct Cumulative totals year to date

Re: Distinct Cumulative totals year to date

Re: Distinct Cumulative totals year to date

Re: Distinct Cumulative totals year to date

Re: Distinct Cumulative totals year to date

Click image to register for webinar

Classroom Training Available!