topic Re: Proc SQL Select Distinct in SAS Programming

Proc SQL Select Distinct

eugenia67 — Thu, 17 Aug 2017 17:16:58 GMT

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

Re: Proc SQL Select Distinct

Reeza — Thu, 17 Aug 2017 17:25:59 GMT

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

Re: Proc SQL Select Distinct

ballardw — Thu, 17 Aug 2017 19:17:24 GMT

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

Re: Proc SQL Select Distinct

Tom — Fri, 18 Aug 2017 03:26:18 GMT

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;

Re: Proc SQL Select Distinct

eugenia67 — Wed, 23 Aug 2017 10:22:49 GMT

It worked!

Thank you!