Proc SQL Select Distinct

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Proc SQL Select Distinct

Hello everyone, 

 

I have a  Proc sql select distinct programm on SAS that counts the number of code (one code desginates individuals pertaining to the same group, so code 5 designates all individuals in group 5) by company: 

PROC SQL;
CREATE TABLE nbcode AS
SELECT DISTINCT code, COUNT(code) AS numbercode
FROM work.example
GROUP BY COMP;
QUIT;

 

It gives me something like this: 

code          nombrecode

1                  1

2                  1

3                  1

4                 10

5                   1

5                6

6                 1

 

That means that; for example, 6 individuals of group 5 are in one company and one individual of group 5 is in a company alone. 

 

I would like to know how to add to this table the company identifier (designated by the variable COMP). I thought the by comp would put automatically the comp identifier in a column to the right, but i was wrong, and i can not manage to do this ...

 

Could you help me please?

 

More generally, how to add other variables we wish to have in a select distinct table?

 

Thank you very much for your help, 

Best, 

Eugenie


Accepted Solutions
Solution
‎08-23-2017 06:22 AM
Super User
Super User
Posts: 7,399

Re: Proc SQL Select Distinct

[ Edited ]
Posted in reply to eugenia67

If you want a variable in the output then it needs to be listed in the variable list part of the SELECT statement.

Do you want the list of CODE values nested inside the list of COMP values?

If you want to count how many times each COMP x CODE combination appears you would want this code:

proc sql;
create table nbcode as
  select comp
       , code
       , count(*) as nobs
  from work.example
  group by 1,2
;
quit;

If you wanted to count how many distinct value of CODE appear within each value of COMP you would want this code:

proc sql;
create table nbcode as
  select comp
       , count(distinct code) as ncodes
  from work.example
  group by 1
;
quit;

 

 

 

 

View solution in original post


All Replies
Super User
Posts: 21,546

Re: Proc SQL Select Distinct

Posted in reply to eugenia67

Post sample data that shows what you have and what you want.

Super User
Posts: 12,148

Re: Proc SQL Select Distinct

Posted in reply to eugenia67

Did you try putting the Comp variable on the select statement?

Solution
‎08-23-2017 06:22 AM
Super User
Super User
Posts: 7,399

Re: Proc SQL Select Distinct

[ Edited ]
Posted in reply to eugenia67

If you want a variable in the output then it needs to be listed in the variable list part of the SELECT statement.

Do you want the list of CODE values nested inside the list of COMP values?

If you want to count how many times each COMP x CODE combination appears you would want this code:

proc sql;
create table nbcode as
  select comp
       , code
       , count(*) as nobs
  from work.example
  group by 1,2
;
quit;

If you wanted to count how many distinct value of CODE appear within each value of COMP you would want this code:

proc sql;
create table nbcode as
  select comp
       , count(distinct code) as ncodes
  from work.example
  group by 1
;
quit;

 

 

 

 

Occasional Contributor
Posts: 11

Re: Proc SQL Select Distinct

It worked! 

 

Thank you! 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 201 views
  • 1 like
  • 4 in conversation