BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vraj1
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

Well guessing again:

proc sql; 

  create table WANT as

  select product, comp, count(subj) as result

  from   HAVE

  group by product, comp;

quit;

vraj1
Quartz | Level 8

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

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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