Help using Base SAS procedures

distinct counts using proc sql

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 115
Accepted Solution

distinct counts using proc sql

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


Accepted Solutions
Solution
‎06-16-2016 06:12 AM
Super User
Super User
Posts: 7,417

Re: distinct counts using proc sql

Well guessing again:

proc sql; 

  create table WANT as

  select product, comp, count(subj) as result

  from   HAVE

  group by product, comp;

quit;

View solution in original post


All Replies
Super User
Super User
Posts: 7,417

Re: distinct counts using proc sql

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;
Frequent Contributor
Posts: 115

Re: distinct counts using proc sql

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

Super User
Super User
Posts: 7,417

Re: distinct counts using proc sql

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...

Frequent Contributor
Posts: 115

Re: distinct counts using proc sql

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

Solution
‎06-16-2016 06:12 AM
Super User
Super User
Posts: 7,417

Re: distinct counts using proc sql

Well guessing again:

proc sql; 

  create table WANT as

  select product, comp, count(subj) as result

  from   HAVE

  group by product, comp;

quit;

Frequent Contributor
Posts: 115

Re: distinct counts using proc sql

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
☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 354 views
  • 0 likes
  • 2 in conversation