```Date: Tue, 21 Aug 2007 09:21:42 -0400 Reply-To: Jack Clark Sender: "SAS(r) Discussion" From: Jack Clark Subject: Re: to calculate percentage difference Comments: To: anitha In-Reply-To: A<1187700253.838161.9280@l22g2000prc.googlegroups.com> Content-Type: text/plain; charset="us-ascii" Hello, I would create a data set with every Customer Number and observations for all 5 months, then merge it to your data to guarantee you have 5 months worth of data for each customer. I am not totally clear on the calculation you want to do from month to month. The sample code I provided checks the difference in PER_SALES from month to month for each customer. If it is something other than that, please write back. * sample data ; data transaction; input custnum \$ month sale_mon tot_sales per_sales; cards; 1004159 1 75000 120240 62.375249501 1004159 2 24210 120240 20.134730539 1004159 3 21000 120240 17.46506986 1004159 5 30 120240 0.0249500998 1014885 1 9100 48490 18.766756032 1014885 2 22890 48490 47.205609404 1014885 4 16500 48490 34.027634564 ; run; proc sort data = transaction; by custnum month; run; * get unique values of custnum along with tot_sales ; proc sql; create table cust as select distinct custnum, tot_sales from transaction ; quit; * create data set with every custnum and all 5 months ; data cust5 (drop=i); set cust; do i = 1 to 5; month = i; output; end; run; * merge with original data and calculate monthly differences ; data need; merge transaction cust5; by custnum month; if sale_mon = . then sale_mon = 0; if per_sales = . then per_sales = 0; pdiff = per_sales - lag(per_sales); if month = 1 then pdiff = .; run; proc print data = need; run; Hope this helps. Jack Clark hi we have data for first five months as given below in which we calculated percentage for sales in each month(per_sales).but v have to calculate percentage difference for each two concecutive months like 2 to 1,3-2,4-3,5-4.the problem s that some months are missing in that percentage. so, v have to take 0 for that missing months and do percentage difference. so if u knw ans for this pls mail me. Percent change in sales in each month starting from month 2 (pct_sls_2_1, pct_sls_3_2, pct_sls_4_3, pct_sls_5_4) data transaction; input custnum \$ month sale_mon tot_sales per_sales; cards; 1004159 1 75000 120240 62.375249501 1004159 2 24210 120240 20.134730539 1004159 3 21000 120240 17.46506986 1004159 5 30 120240 0.0249500998 1014885 1 9100 48490 18.766756032 1014885 2 22890 48490 47.205609404 1014885 4 16500 48490 34.027634564 run; ```

Back to: Top of message | Previous page | Main SAS-L page