03-03-2016 01:23 PM - edited 03-03-2016 01:30 PM
my data has ID and stores where they put orders. I want to calculate how many stores one ID has put orders with, and have several steps code, and wonder if someone can help imporve it by making it shorter.
/* first, only select ID and Store columns to make data size smaller */ proc sql; create table table1 as select ID, store from mydata; quit; /* count how many stores one ID has put orders with */ /* this step, in the output data, the same ID listed n times if n stores */ proc sql; create table table2 as select ID, store, count(*) as how_many_stores_per_ID from table1 group by ID; quit; /* to remove duplicate rows for the same ID */ proc sql; create table table3 as select distinct ID, how_many_stores_per_ID from table2; quit; /* to verify if the sum of all counts equal to the total rows of mydata */ proc means data=table3 n sum ; var how_many_stores_per_ID; output n=count sum=total1; run;
I tested, they all worked, and the sum(how_many_stores_per_ID) equals total rows of mydata, which means it is right.
03-03-2016 01:29 PM - edited 03-03-2016 01:44 PM
I was thinking to do like below, which gave different results. which one is correct, the above moethod or the one below? Many thanks.
create table table4 as
select ID, store, count(*) as how_many_stores_per_ID
group by ID, store;
03-03-2016 02:03 PM
You skipped the step where you removed duplicate rows for ID by grouping by ID and Store. Also, you can do this pulling from your mydata dataset, eliminating the first step. Try the code below:
proc sql; create table table4 as select ID, count(store) as how_many_stores_per_ID from mydata group by ID; quit;
03-03-2016 02:12 PM
If you use group by ID, store then you will get duplicate ID's if they are related to more than one store. In your original code, you grouped by ID, store; however, you then proceeded to eliminate duplicate IDs with your select distinct statement. You can group just by ID, and that will give you a count of all the stores for each unique ID.
Need further help from the community? Please ask a new question.