distinct counts using proc sql

[vraj1]

i am using the below code to display and count distinct subjects but i get the same number for all 3 reportings.

proc sql;
create table inc as
select distinct reporting, product, comparator, comparator2, randomisation_scheme,
"All Subjects" as txt,count(distinct subj_id) as cnt
from subj
order by trl_id_reporting;
quit;

Can anyone help me on this

[RW9]

To do aggregates in SQL you use the keywords;

group by

Also your code will not work, you are refering to variables that are not in the select clause, maybe you want: something like the below - its hard to say as you have not posted any test data (in the form of a datastep) and what the output should look like, so we are guessing.

proc sql;
  create table INC as
  select REPORTING,
            PRODUCT,
            COMPARATOR,
            COMPARATOR2,
            RANDOMISATION_SCHEME,
            "All Subjects" as TXT,
            count(distinct SUBJ_ID) as CNT
  from   SUBJ
  group by REPORTING,
                 PRODUCT,
                 COMPARATOR,
                COMPARATOR2,
                 RANDOMISATION_SCHEME,
                "All Subjects";
quit;

[vraj1]

I tried it but it didnt work as i will get all the 400 obs. i used distinct before reporting but still no luck

[RW9]

Sorry, I have no idea what your talking about.  Post test data - in the form of a datastep - and what the output should look like.

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[vraj1]

sorry for not being clear output should be distinct of reporting terms with product, comp and there respective number of subjects for each reporting

[RW9 · Accepted solution]

Well guessing again:

proc sql; 

  create table WANT as

  select product, comp, count(subj) as result

  from   HAVE

  group by product, comp;

quit;

[vraj1]

test data

reporting	subjid	product	comparator	comp2
FSB-213	2	ins	none	none
FSB-213	1	ins2	pokf	okij
FSB-213	23	ins3	pokf	okij
FSB-213	11	ins4	pokf	okij
FSB-213	21,66667	ins5	pokf	okij
FSB-340	26,66667	ins6	pokf	okij
FSB-340	31,66667	ins7	pokf	okij
FSB-340	36,66667	ins8	pokf	okij