BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
fengyuwuzu
Pyrite | Level 9

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.

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

One step should be enough:

select id, count(distinct store)
Data never sleeps

View solution in original post

11 REPLIES 11
fengyuwuzu
Pyrite | Level 9

I was thinking to do like below, which gave different results. which one is correct, the above moethod or the one below? Many thanks.

 

proc sql;
create table table4 as
select ID, store, count(*) as how_many_stores_per_ID
from table1
group by ID, store;
quit;

 

dcruik
Lapis Lazuli | Level 10

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;

 

 

 

fengyuwuzu
Pyrite | Level 9
I am confused with: should I use group by ID or
group by ID, store?
dcruik
Lapis Lazuli | Level 10

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.

Reeza
Super User

Post sample data.

Test it manually with a small subset of data. You know what the answer should be, so you'll know which code is correct.

fengyuwuzu
Pyrite | Level 9
 
LinusH
Tourmaline | Level 20

One step should be enough:

select id, count(distinct store)
Data never sleeps
fengyuwuzu
Pyrite | Level 9

and use group by id?

LinusH
Tourmaline | Level 20

Yep

Data never sleeps
LinusH
Tourmaline | Level 20

Did the count(distinct...) work for you?

If yes, mark as solved.

Data never sleeps
fengyuwuzu
Pyrite | Level 9
yes, it is the simplest code.
Thanks

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1239 views
  • 2 likes
  • 4 in conversation