Proc SQL Select Distinct

[eugenia67]

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

[Reeza]

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

[ballardw]

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

[Tom · Accepted solution]

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;

[eugenia67]

It worked! 

Thank you!