Hi ,
I have a sales dataset, Please find the sample file in attachment section.
I want to group the sales on basis of Prod and then for Year and then for Month.
Means If I want to know
Thanks!
Sample data look like this...
C_ID | Product_ID | P_CATEGORY | CONDTION | Brand | Price | Selling_price | Coupon_ID | Date |
4595 | 534 | SHOES | New | RKFCM | $1,484.74 | $1,471.74 | BN710 | 26/08/2014 |
6679 | 396 | LUGGAGE | New | TNVON | $1,452.50 | $1,442.50 | KF815 | 26/01/2015 |
7075 | 449 | SHOES | Refurbished | VKKEA | $4,308.02 | $4,284.02 | UG960 | 03/01/2015 |
1418 | 145 | BEDDING | New | PIUEM | $3,272.59 | $3,249.59 | DK471 | 30/05/2014 |
6740 | 844 | ELECTRONICS | Used | FMBZC | $628.67 | $608.67 | OJ311 | 14/08/2014 |
4305 | 922 | LUGGAGE | New | IWPJG | $3,788.97 | $3,740.97 | XB493 | 18/08/2014 |
4477 | 616 | OFFICE SUPPLIES | Used | EVWJY | $1,011.90 | $1,001.90 | LU503 | 22/04/2014 |
4672 | 300 | APPLIANCES | New | KZDWR | $4,918.58 | $4,891.58 | UX835 | 20/01/2015 |
5481 | 827 | ELECTRONICS | New | OULOW | $2,830.51 | $2,802.51 | KG804 | 12/05/2014 |
5974 | 470 | LUGGAGE | Used | TNVON | $4,783.25 | $4,764.25 | ER876 | 23/09/2014 |
7759 | 266 | COMPUTERS | Used | GCVNE | $2,143.08 | $2,098.08 | MS262 | 03/07/2015 |
3705 | 236 | ELECTRONICS | Used | BEFJD | $4,195.69 | $4,172.69 | SZ440 | 02/08/2014 |
6391 | 694 | BABY CLOTHING | Used | GOZLI | $3,967.19 | $3,918.19 | GX363 | 17/02/2015 |
8844 | 832 | DECOR | New | ZACKV | $1,023.49 | $974.49 | SZ707 | 26/07/2014 |
1256 | 722 | GAMES | Refurbished | EITXF | $2,421.99 | $2,383.99 | HQ715 | 12/11/2014 |
3723 | 710 | KITCHEN & DINING | New | RGMIK | $663.82 | $650.82 | ZG209 | 26/04/2014 |
3833 | 763 | DECOR | Refurbished | LBVIW | $4,943.44 | $4,924.44 | MD699 | 27/06/2014 |
6565 | 396 | LUGGAGE | New | TNVON | $1,452.50 | $1,439.50 | FU406 | 24/03/2014 |
4852 | 136 | KITCHEN & DINING | New | NSILF | $4,019.79 | $3,984.79 | OY795 | 21/02/2014 |
4139 | 295 | COMPUTERS | New | YEJZI | $2,868.97 | $2,827.97 | XU183 | 05/11/2014 |
6814 | 988 | COMPUTERS | Used | DCJRW | $865.57 | $846.57 | ZY761 | 24/06/2014 |
4632 | 722 | GAMES | Refurbished | EITXF | $2,421.99 | $2,403.99 | UE270 | 03/02/2014 |
7589 | 183 | LUGGAGE | New | CENQJ | $404.74 | $376.74 | BW423 | 03/04/2014 |
8532 | 849 | ELECTRONICS | Refurbished | FMBZC | $3,697.22 | $3,655.22 | SO118 | 03/11/2015 |
1256 | 827 | ELECTRONICS | New | OULOW | $2,830.51 | $2,784.51 | BD959 | 25/12/2014 |
2019 | 554 | SHOES | New | RKFCM | $3,461.72 | $3,434.72 | BQ577 | 09/05/2014 |
3024 | 484 | OFFICE SUPPLIES | Used | WELZA | $2,205.71 | $2,167.71 | PZ797 | 10/12/2014 |
4879 | 552 | SHOES | New | RKFCM | $1,257.44 | $1,235.44 | NB862 | 20/02/2015 |
7979 | 389 | CLOTHING | New | GTFFL | $3,747.69 | $3,704.69 | JL124 | 17/01/2014 |
8919 | 579 | SHOES | Refurbished | RKFCM | $2,298.99 | $2,262.99 | YU458 | 11/07/2014 |
1831 | 136 | KITCHEN & DINING | New | NSILF | $4,019.79 | $4,007.79 | RJ810 | 10/03/2014 |
3500 | 140 | OFFICE SUPPLIES | Used | WELZA | $2,513.31 | $2,490.31 | QG119 | 04/03/2015 |
9950 | 544 | ELECTRONICS | Used | FMBZC | $781.93 | $771.93 | TE137 | 07/04/2014 |
2011 | 406 | CLOTHING | Used | ONKHS | $2,202.20 | $2,181.20 | IV500 | 05/03/2014 |
2297 | 236 | ELECTRONICS | Used | BEFJD | $4,195.69 | $4,146.69 | VR794 | 04/06/2014 |
3199 | 687 | SHOES | Used | RKFCM | $3,827.74 | $3,783.74 | RX100 | 19/09/2014 |
4729 | 996 | CLOTHING | New | GTFFL | $925.56 | $887.56 | FN159 | 18/06/2014 |
9275 | 775 | BABY TOYS | Refurbished | CTAOI | $379.49 | $347.49 | ZV809 | 16/10/2014 |
2304 | 956 | SHOES | Used | DYHNA | $2,800.18 | $2,751.18 | GP301 | 25/01/2015 |
4852 | 539 | ELECTRONICS | New | OULOW | $668.11 | $621.11 | VE748 | 28/01/2015 |
5432 | 300 | COMPUTERS | Used | YXQFM | $2,455.58 | $2,435.58 | YM653 | 31/08/2014 |
2200 | 554 | SHOES | New | RKFCM | $3,461.72 | $3,432.72 | LT350 | 18/07/2014 |
6042 | 381 | OFFICE SUPPLIES | New | WELZA | $4,694.60 | $4,652.60 | IZ391 | 02/11/2015 |
9856 | 406 | CLOTHING | Refurbished | ZVYTC | $393.59 | $358.59 | LF155 | 24/06/2014 |
3598 | 381 | GAMES | New | HWAVW | $751.39 | $711.39 | UK998 | 29/03/2014 |
3970 | 554 | KITCHEN & DINING | New | RGMIK | $4,185.27 | $4,171.27 | NQ702 | 02/12/2014 |
8565 | 699 | CLOTHING | New | KXUWE | $1,061.81 | $1,025.81 | ZF197 | 01/01/2014 |
5081 | 970 | SHOES | New | DYHNA | $387.88 | $369.88 | UE386 | 01/10/2015 |
8532 | 544 | APPLIANCES | Used | BUDLO | $2,135.85 | $2,085.85 | WR271 | 27/04/2014 |
8794 | 138 | BABY TOYS | Used | TPDLE | $3,521.43 | $3,499.43 | MR560 | 31/08/2014 |
5850 | 752 | CLOTHING | New | ZVYTC | $2,215.14 | $2,189.14 | UH554 | 16/06/2014 |
6286 | 527 | KITCHEN & DINING | Used | ZGHYW | $115.43 | $94.43 | IC659 | 01/08/2014 |
6502 | 662 | BABY CLOTHING | New | CTAOI | $4,658.93 | $4,633.93 | FX696 | 01/09/2015 |
9756 | 527 | BABY CLOTHING | New | BGYXR | $3,309.99 | $3,297.99 | MO330 | 29/06/2014 |
4575 | 838 | BEDDING | Refurbished | CJUEU | $1,980.83 | $1,955.83 | VE651 | 22/09/2014 |
4540 | 544 | ELECTRONICS | Used | FMBZC | $781.93 | $746.93 | WP978 | 13/11/2014 |
6143 | 435 | GAMES | New | HWAVW | $4,157.59 | $4,134.59 | EL485 | 28/03/2015 |
6224 | 195 | BEDDING | Used | CJUEU | $3,757.01 | $3,710.01 | DJ214 | 25/01/2014 |
7075 | 236 | ELECTRONICS | Used | BEFJD | $4,195.69 | $4,166.69 | WP466 | 21/03/2014 |
9275 | 572 | LUGGAGE | New | CENQJ | $3,277.74 | $3,264.74 | AG242 | 01/09/2015 |
5974 | 616 | BABY TOYS | Refurbished | BGYXR | $378.28 | $334.28 | PK588 | 03/04/2014 |
6740 | 644 | CLOTHING | Used | KXUWE | $623.05 | $591.05 | FW703 | 27/03/2014 |
6740 | 295 | COMPUTERS | New | YEJZI | $2,868.97 | $2,836.97 | RA454 | 01/07/2014 |
9305 | 119 | OFFICE SUPPLIES | Refurbished | EVWJY | $3,130.29 | $3,103.29 | CI225 | 19/03/2015 |
4336 | 795 | CLOTHING | Refurbished | ONKHS | $2,667.33 | $2,644.33 | IA355 | 27/10/2014 |
7433 | 844 | BEDDING | New | RKCPD | $3,559.28 | $3,527.28 | LH644 | 05/03/2014 |
8919 | 907 | DECOR | Used | LBVIW | $2,661.83 | $2,636.83 | GA750 | 19/09/2014 |
9532 | 151 | COMPUTERS | New | DCJRW | $1,363.08 | $1,337.08 | NJ980 | 15/10/2014 |
It's actually not clear what you want out of this, the question is vague.
You say "month on month" sales increase for 2014 to 2015? Is that month to month changes in prices (Sept 2014 to Oct 2015), or is that Sep 2014 to Sept 2015. There's a bunch of things you'll also need to consider, such as, do you have sales in all months? This is a relatively simple question but there are several steps that have to happen (doing it the 'easy' way) so it would be good to see your general approach before someone puts out a solution that won't work for you at all.
If you're new to SAS I would suggest using SAS Studio or EG, using the GUI and see the code that's generated. Use that code and modify as needed.
Video tutorials are here.
http://support.sas.com/training/tutorial/
Sorry About the Confusion:
Now I have taken a general dataset given below
data Sales;
input Prod_id Price Date mmddyy10.;
Format Date mmddyy10.;
cards;
1 100 01/01/2014
1 100 01/10/2014
2 100 03/01/2014
1 100 04/01/2014
2 100 01/02/2014
2 100 05/01/2014
1 100 05/02/2014
2 110 01/01/2015
1 110 01/10/2015
2 110 03/01/2015
1 110 04/01/2015
2 110 01/02/2015
1 110 05/01/2015
2 110 05/02/2015
;
run
;
****My aproach*****;
Data New_sales;
Set Sales;
Month=month(date);
Year=Year(Date);
run;
Proc sort data=new_sales;
by Prod_id year Month;
run;
data Final_sales;
set new_sales;
by Prod_id Year Month;
if First.Prod_id then do;
Prod_sum=0;
if First.year then do;
Year_sum=0;
Month_sum=0;
End;
else Year_sum+Month_sum;
if First.Month then Month_sum=0;
else Month_sum+Price;
end;
If Last.Prod_id;
run;
****Looking for desired output:
Prod_id Year Month Price
1 2014 1 200
1 2014 4 100
1 2014 5 100
1 2015 1 110
1 2015 4 110
1 2015 5 110
2 2014 1 100
2 2014 3 100
2 2014 5 100
2 2015 1 220
2 2015 3 110
2 2015 5 110
Thanks!
Below code returns your desired result.
data Sales;
input Prod_id Price Date :mmddyy10.;
Format Date mmddyy10.;
cards;
1 100 01/01/2014
1 100 01/10/2014
2 100 03/01/2014
1 100 04/01/2014
2 100 01/02/2014
2 100 05/01/2014
1 100 05/02/2014
2 110 01/01/2015
1 110 01/10/2015
2 110 03/01/2015
1 110 04/01/2015
2 110 01/02/2015
1 110 05/01/2015
2 110 05/02/2015
;
run;
proc sql feedback;
create table want as
select
prod_id,
end_of_month,
month(end_of_month) as month,
year(end_of_month) as year,
sum(price) as sum_price
from
(
select
prod_id,
intnx('month',date,0,'e') as end_of_month format=date9.,
price
from sales
)
group by prod_id, end_of_month
;
quit;
Here you go:
data Sales;
input Prod_id Price Date :mmddyy10.;
Format Date mmddyy10.;
cards;
1 100 01/01/2014
1 100 01/10/2014
2 100 03/01/2014
1 100 04/01/2014
2 100 01/02/2014
2 100 05/01/2014
1 100 05/02/2014
2 110 01/01/2015
1 110 01/10/2015
2 110 03/01/2015
1 110 04/01/2015
2 110 01/02/2015
1 110 05/01/2015
2 110 05/02/2015
;
run;
data inter;
set Sales;
format end_of_month date9.;
end_of_month=intnx('month',date,0,'e');
run;
proc sort data=inter out=inter;
by Prod_id end_of_month;
run;
data want;
set inter;
by Prod_id end_of_month;
retain sum_price;
sum_price=sum(sum_price, price);
if last.end_of_month then
do;
output;
call missing(sum_price);
end;
run;
It doesn't make sense to do this in a datastep, unless it's homework.
@LittlesasMaster wrote:
Hi ,
I have a sales dataset, Please find the sample file in attachment section.
I want to group the sales on basis of Prod and then for Year and then for Month.
Means If I want to know
- What is the month on month sale increase for the years 2015 and year 2014 for new and refurbished products?
Thanks!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.