topic Re: distinct counts using proc sql in SAS Procedures

distinct counts using proc sql

vraj1 — Thu, 16 Jun 2016 08:38:20 GMT

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

Re: distinct counts using proc sql

RW9 — Thu, 16 Jun 2016 08:43:23 GMT

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;

Re: distinct counts using proc sql

vraj1 — Thu, 16 Jun 2016 08:47:38 GMT

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

Re: distinct counts using proc sql

RW9 — Thu, 16 Jun 2016 08:49:00 GMT

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-data-AKA-generate/ta-p/258712

Re: distinct counts using proc sql

vraj1 — Thu, 16 Jun 2016 08:49:06 GMT

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

Re: distinct counts using proc sql

RW9 — Thu, 16 Jun 2016 08:50:36 GMT

Well guessing again:

proc sql;

create table WANT as

select product, comp, count(subj) as result

from HAVE

group by product, comp;

quit;

Re: distinct counts using proc sql

vraj1 — Thu, 16 Jun 2016 08:58:52 GMT

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