topic Re: grouping of vars with First. and last. automatic vars in SAS Programming

grouping of vars with First. and last. automatic vars

LittlesasMaster — Sat, 17 Jun 2017 16:45:09 GMT

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!

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

LittlesasMaster — Sat, 17 Jun 2017 17:18:19 GMT

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

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

LinusH — Sat, 17 Jun 2017 22:01:30 GMT

And what have you tried?

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

Reeza — Sun, 18 Jun 2017 00:30:48 GMT

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/

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

LittlesasMaster — Sun, 18 Jun 2017 03:33:26 GMT

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!

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

Patrick — Sun, 18 Jun 2017 04:40:42 GMT

@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;

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

LittlesasMaster — Sun, 18 Jun 2017 04:36:39 GMT

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. -##

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

Patrick — Sun, 18 Jun 2017 04:41:14 GMT

@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;

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

Reeza — Sun, 18 Jun 2017 06:44:21 GMT

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!