DATA Step, Macro, Functions and more

grouping of vars with First. and last. automatic vars

Reply
Contributor
Posts: 37

grouping of vars with First. and last. automatic vars

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!

Contributor
Posts: 37

Re: grouping of vars with First. and last. automatic vars

Posted in reply to LittlesasMaster

 

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

 

Super User
Posts: 5,424

Re: grouping of vars with First. and last. automatic vars

Posted in reply to LittlesasMaster
And what have you tried?
Data never sleeps
Super User
Posts: 19,770

Re: grouping of vars with First. and last. automatic vars

Posted in reply to LittlesasMaster

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/

 

Contributor
Posts: 37

Re: grouping of vars with First. and last. automatic vars

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!

Respected Advisor
Posts: 4,173

Re: grouping of vars with First. and last. automatic vars

[ Edited ]
Posted in reply to LittlesasMaster

@LittlesasMaster

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;

 

 

Contributor
Posts: 37

Re: grouping of vars with First. and last. automatic vars

Thanks for replying,
Can you please do this using datastep?
I am curious to learn this via datastep.
Please.

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Respected Advisor
Posts: 4,173

Re: grouping of vars with First. and last. automatic vars

Posted in reply to LittlesasMaster

@LittlesasMaster

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;

Super User
Posts: 19,770

Re: grouping of vars with First. and last. automatic vars

Posted in reply to LittlesasMaster

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!


 

Ask a Question
Discussion stats
  • 8 replies
  • 485 views
  • 0 likes
  • 4 in conversation