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.
... View more