topic Re: Distinct Cumulative totals year to date in SAS Programming

Distinct Cumulative totals year to date

NewSASPerson — Mon, 18 Nov 2019 20:17:27 GMT

I would like to get the total distinct values by month and year to date. The logic in my code does the sum and I can't think through how to get the distinct values. I have attached a worksheet with the sample data.

This is my code

data WORK.BOOK4;
  infile datalines dsd truncover;
  input ACTUAL:DOLLAR20.2 COUNTRY:$6. PRODTYPE:$9. PRODUCT:$20. YEAR:F12. MONTH:$3.;
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, month
order  by country,  prodtype, month;
quit;

data ytd (rename=(ytd_Actual = Actual ytd_type = Prod_Type));
set cur_month;
by  country prodtype;

if first.prodtype then ytd_actual = 0;
ytd_actual+ actual;

if first.prodtype then ytd_type = 0;
ytd_type + prod_type;

Type = "YTD";

drop  actual prod_type;
run;

data combined;
	set cur_month ytd;

run;

This is the output I get, The issues is on the Prod_Type column

COUNTRY	PRODTYPE	MONTH	Type		Actual	Prod_Type
CANADA	FURNITURE	Jan	Current Month	2548	4
CANADA	FURNITURE	Feb	Current Month	2964	5
CANADA	FURNITURE	Mar	Current Month	2870	4
CANADA	FURNITURE	Apr	Current Month	3960	5
CANADA	FURNITURE	May	Current Month	3731	4
CANADA	FURNITURE	Jun	Current Month	3570	4
CANADA	FURNITURE	Jul	Current Month	1869	4
CANADA	FURNITURE	Aug	Current Month	2346	4
CANADA	FURNITURE	Sep	Current Month	2504	4
CANADA	FURNITURE	Oct	Current Month	1848	4
CANADA	FURNITURE	Nov	Current Month	1889	5
CANADA	FURNITURE	Dec	Current Month	3034	5
CANADA	FURNITURE	Jan	YTD		2548	4
CANADA	FURNITURE	Feb	YTD		5512	9
CANADA	FURNITURE	Mar	YTD		8382	13
CANADA	FURNITURE	Apr	YTD		12342	18
CANADA	FURNITURE	May	YTD		16073	22
CANADA	FURNITURE	Jun	YTD		19643	26
CANADA	FURNITURE	Jul	YTD		21512	30
CANADA	FURNITURE	Aug	YTD		23858	34
CANADA	FURNITURE	Sep	YTD		26362	38
CANADA	FURNITURE	Oct	YTD		28210	42
CANADA	FURNITURE	Nov	YTD		30099	47
CANADA	FURNITURE	Dec	YTD		33133	52

However this is my desired output.

COUNTRY PRODTYPE MONTH Type 	   	Actual Prod_Type 
CANADA FURNITURE Jan Current Month 	2548 	4 
CANADA FURNITURE Feb Current Month 	2964 	5 
CANADA FURNITURE Mar Current Month 	2870 	4 
CANADA FURNITURE Apr Current Month 	3960 	5 
CANADA FURNITURE May Current Month 	3731 	4 
CANADA FURNITURE Jun Current Month 	3570 	4 
CANADA FURNITURE Jul Current Month 	1869	4 
CANADA FURNITURE Aug Current Month 	2346 	4 
CANADA FURNITURE Sep Current Month 	2504 	4 
CANADA FURNITURE Oct Current Month 	1848 	4 
CANADA FURNITURE Nov Current Month	1889 	5 
CANADA FURNITURE Dec Current Month 	3034 	5 
CANADA FURNITURE Jan YTD 		2548 	4 sofa, bed table, desk
CANADA FURNITURE Feb YTD 		5512 	5 chair
CANADA FURNITURE Mar YTD	        8382 	7 dresser, ottoman
CANADA FURNITURE Apr YTD 		12342 	8 bench
CANADA FURNITURE May YTD 		16073 	9 nightstand
CANADA FURNITURE Jun YTD 		19643 	9 
CANADA FURNITURE Jul YTD 		21512 	9 
CANADA FURNITURE Aug YTD 		23858 	10 stool
CANADA FURNITURE Sep YTD 		26362 	10 
CANADA FURNITURE Oct YTD 		28210 	10 
CANADA FURNITURE Nov YTD 		30099 	11 loveseat
CANADA FURNITURE Dec YTD 		33133 	12 cupboard

Any assistance I can get will be greatly appreciated.

Re: Distinct Cumulative totals year to date

PaigeMiller — Mon, 18 Nov 2019 18:30:58 GMT

Please explain how March YTD in the desired output is 7. How is this computed?

Also, where does the "dresser, ottoman" come from?

Re: Distinct Cumulative totals year to date

Reeza — Mon, 18 Nov 2019 18:32:59 GMT

You likely need to also include a bit of what your starting data looks like.

Re: Distinct Cumulative totals year to date

ballardw — Mon, 18 Nov 2019 18:50:48 GMT

Any attachment of data apparently didn't make it.

Better is to provide data step code of data and paste as text in a code box:

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: Distinct Cumulative totals year to date

NewSASPerson — Mon, 18 Nov 2019 19:09:31 GMT

I just added the data (book4.xlsx)

Re: Distinct Cumulative totals year to date

NewSASPerson — Mon, 18 Nov 2019 19:08:54 GMT

I have updated the post with the data. Please advise if you can see it. Thank you.

Re: Distinct Cumulative totals year to date

Kurt_Bremser — Mon, 18 Nov 2019 19:31:52 GMT

Please read @ballardw's post again. Excel spreadsheets are not SAS datasets.

Re: Distinct Cumulative totals year to date

PaigeMiller — Mon, 18 Nov 2019 19:55:04 GMT

@NewSASPerson wrote:
I have updated the post with the data. Please advise if you can see it. Thank you.

Many of us will not open Microsoft Office documents because they are a security threat. Please provide the data as described in the link from @ballardw

Also please explain how, in the desired output, March YTD is equal to 7. What are the calculations?

Re: Distinct Cumulative totals year to date

mkeintz — Mon, 18 Nov 2019 20:01:28 GMT

Instead of adding a spreadsheet, you'll probably get a lot faster response, and more responses, if you utilize the advice from @ballardw :

Better is to provide data step code of data and paste as text in a code box:

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

I know that I am always more ready to provide actual code when the person asking for help creates a sas data step appropriate to her question. Help us help you.

Regards,

Mark

Re: Distinct Cumulative totals year to date

NewSASPerson — Mon, 18 Nov 2019 20:18:56 GMT

I have update the code. It should have the data step now. Thank you.

Re: Distinct Cumulative totals year to date

NewSASPerson — Mon, 18 Nov 2019 20:35:16 GMT

I have update the code. It should have the data step now. Thank you.

Re: Distinct Cumulative totals year to date

ballardw — Mon, 18 Nov 2019 22:49:57 GMT

I don't duplicate your output.

The data from the first proc SQL creating WORK.CUR_MONTH has the output in Alphabetical Month Order: Apr, Aug, Dec etc.

If you are doing something such that MONTH is numeric 1 to 12 it is not shown in the code.

Re: Distinct Cumulative totals year to date

mkeintz — Tue, 19 Nov 2019 04:09:28 GMT

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;

Re: Distinct Cumulative totals year to date

NewSASPerson — Tue, 19 Nov 2019 07:49:18 GMT

Thank you for your help in fixing the data step. However, this does not give me the desire result. It gives the same result I already have.

Re: Distinct Cumulative totals year to date

mkeintz — Tue, 19 Nov 2019 16:57:24 GMT

Ah, it's the list of newly-encountered products you want included in the YTD records. To do this in the data step, you can use a sorted version of your original dataset (by country prodtype month).

Such a dataset can be interleaved with the results of your sql procedure (by country prodtype monthnum), such that for each month, all the book4 products (sorted below) will be read, followed by the single record from the sql data set. This program should do:

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,
    count(distinct product) as Prod_Type
  from book4
  group by country,  prodtype, monthnum,  month
  order  by country,  prodtype, monthnum, month;
quit;

proc sort data=book4 out=book4s;
  by country prodtype year monthnum;
run;

data want (drop=cum_: product);
  do until (last.prodtype);
    set cur_month ;
    by country prodtype;
    output;
  end;

  length cum_list $300 prod_list $50;
  do until (last.prodtype);
    set book4s (keep=country prodtype monthnum product in=inprod) 
        cur_month ;
    by country prodtype monthnum;
	if first.monthnum then call missing(prod_list);
    if inprod then do;
      if findw(cum_list,trim(product),' ','E')=0 then do;
        prod_list=catx(' ',prod_list,product);
        cum_list=catx(' ',cum_list,product);
      end;
      continue;  /* Go back to top of loop */
    end;
    cum_actual+actual;
    cum_pt+prod_type;
    type='YTD';
    actual=cum_actual;
    prod_type=cum_pt;
    output;
  end;
run;

How this works:

In the DATA step, the first do loop writes out the results taken from the sql procedure.
In the second do loop,
1. the SET statement reads, for each country/prodtype/monthnum all records from BOOK4S, followed by the single corresponding record from CUR_MONTH.
2. The "if inprod" tests for whether the incoming record is from BOOK4S. For such records the product tests whether the incoming PRODUCT has already been encountered and has been stored in CUM_LIST. If it is not then it is added to CUM_LIST and also to PROD_LIST - the variable that will be output in the YTD record.
3. The CONTINUE statement tell SAS to back to the beginning of the DO loop, which is what is needed for incoming BOOK4S records.
4. Otherwise, if the incoming record is not from BOOK4S (inprod not equal to 1) it is from the sql data set, where ytd values are calculated and output.

Re: Distinct Cumulative totals year to date

NewSASPerson — Tue, 19 Nov 2019 18:58:03 GMT

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.

Re: Distinct Cumulative totals year to date

mkeintz — Tue, 19 Nov 2019 20:32:40 GMT

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;

Re: Distinct Cumulative totals year to date

NewSASPerson — Wed, 20 Nov 2019 02:30:34 GMT

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?

Re: Distinct Cumulative totals year to date

mkeintz — Wed, 20 Nov 2019 03:32:54 GMT

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.

Re: Distinct Cumulative totals year to date

NewSASPerson — Wed, 20 Nov 2019 14:23:57 GMT

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