topic Re: Repeat the same procedure for all observations in SAS Programming

Repeat the same procedure for all observations

m1986MM — Sat, 08 Aug 2015 15:06:39 GMT

Hi everyone,

Below is small part of the dataset that I'm working with. The only difference is that my dataset contains many more company_Nm, branch_id, product_id and date.

Company_Nm	Branch_ID	City	Product_ID	Date	Sale	Competitors_sale
AAA	AAA1	Gainesville	XX	Jan-11	10000
AAA	AAA2	Yreka	XX	Jan-11	8000
AAA	AAA3	Yreka	XX	Jan-11	5000
AAA	AAA4	San Francisco	XX	Jan-11	4000
AAA	AAA5	Los Angeles	XX	Jan-11	7000
AAA	AAA6	Donalsonville	XX	Jan-11	12000
AAA	AAA7	Savannah	XX	Jan-11	80000
BBB	BBB1	Yreka	XX	Jan-11	1000
BBB	BBB2	San Francisco	XX	Jan-11	1000
BBB	BBB3	Los Angeles	XX	Jan-11	1000
BBB	BBB4	Donalsonville	XX	Jan-11	1000
BBB	BBB5	Savannah	XX	Jan-11	1000

For each specific Company_nm and branch_id,each row, I want to find other branches located in the same city, let's say competitors. Then I need to take the average of sales among the competitors for the same product_id and date in that row, and put the value in the competitor_sale column.

Previously, I picked only one branch_id as a pilot and did the same procedure for that. I may be able to extend my code to all rows by do loop, but I'm not sure what's the most efficient way. Below is my code, even though I'm think it's so ugly and inefficient.

data AAA1;

set have;

if branch_id='AAA1';

run;

proc sql;

create table comp1 as

select *

from have

where city in (Select city from AAA1);

quit;

proc sql;

create comp2 as

select *

from comp1

where company_nm not in (Select company_nm from AAA1);

quit;

proc sql ;

create table comp_rate as

select product_id, date,

mean(rate) as Ave_comp format = 5.3

from comp2

group by product_id, date;

quit;

data want;

merge AAA1 comp_rate;

by product_id date;

run;

I really appreciate any and all suggestions.

Re: Repeat the same procedure for all observations

Jagadishkatam — Sat, 08 Aug 2015 17:02:28 GMT

This is an untested code , for every branch_id a dataset is created. Hope this is what you are expecting

proc sql;

select distinct branch_id, count(distinct branch_id) into :id1-:id&sysmaxlong, :obs from have;

quit;

%put &obs;

%macro test;

%do i = 1 %to &obs;

data &&id&i;

set have;

if branch_id="&&id&i";

run;

proc sql;

create table comp1 as

select *

from have

where city in (Select city from &&id&i);

quit;

proc sql;

create table comp2 as

select *

from comp1

where company_nm not in (Select company_nm from &&id&i);

quit;

proc sql ;

create table comp_rate as

select product_id, date,

mean(rate) as Ave_comp format = 5.3

from comp2

group by product_id, date;

quit;

data &&id&i;

merge &&id&i comp_rate;

by product_id date;

run;

%end;

%mend;

Thanks,

Jag

Re: Repeat the same procedure for all observations

PGStats — Sat, 08 Aug 2015 18:49:43 GMT

The following will give you the monthly competitor mean sales by product and city, assuming that other branches from the same company are competitors:

data have;

infile datalines truncover;

input Company_Nm $ Branch_Id $ City &$12. Product_ID $ Date :anydtdte6. Sales;

format date yymmdd10.;

datalines;

AAA AAA1 Gainesville XX Jan-11 10000

AAA AAA2 Yreka XX Jan-11 8000

AAA AAA3 Yreka XX Jan-11 5000

AAA AAA4 San Francisco XX Jan-11 4000

AAA AAA5 Los Angeles XX Jan-11 7000

AAA AAA6 Donalsonville XX Jan-11 12000

AAA AAA7 Savannah XX Jan-11 80000

BBB BBB1 Yreka XX Jan-11 1000

BBB BBB2 San Francisco XX Jan-11 1000

BBB BBB3 Los Angeles XX Jan-11 1000

BBB BBB4 Donalsonville XX Jan-11 1000

BBB BBB5 Savannah XX Jan-11 1000

;

proc sql;

select Company_Nm, Branch_Id, Product_Id, Date, Sales,

(select mean(Sales)

from have

where Product_Id=a.Product_Id and date=a.date and not

(Company_Nm = a.Company_Nm and Branch_Id = a.Branch_Id)

) as Competitor_Sale

from have as a;

quit;