CODEWITHSUNDEEP
sasproc-sql
Nimit_ZZ
Nimit_ZZ

Reputation: 495

Trying to get the unique values of subjects in PROC SQL

Proc SQL NOPRINT;    
        Create Table DSDD as    
        Select DISTINCT (USUBJID),PRIMARY,ARM,ARMN,APTS,DSCOMP_F,APRS,FAS    
            From DERIVED.DSDD    
                Where UPCASE(APTS)="Y";    
Quit

I'm wanted the unique values of USUBJID. BUt when I run the above code I get 512 observations. When I the below code I get the count at 490. I am considering the unique values of USUBJID right? What needs to be done to get the 490 unique observations. 

Proc SQL NOPRINT;    
        Create Table DSDD as    
        Select COUNT(DISTINCT (USUBJID)) as     COUNT,PRIMARY,ARM,ARMN,APTS,DSCOMP_F,APRS,FAS    
            From DERIVED.DSDD    
                Where UPCASE(APTS)="Y";    
Quit

Upvotes: 1

Views: 6670

Answers (1)

Joe
Joe

Reputation: 63434

DISTINCT applies for the entire line, not for a single variable. You can use group by to ask it to give you lines grouped by USUBJID, but you would need to tell it what to do with the various lines - use things like MAX(), MIN(), SUM() to group those variables; you cannot have any variables not in summary functions that aren't in the group by statement, or you will not get your desired result.

Upvotes: 2

Related Questions