Distinct query

[Sandeep77]

Hi all,

I am trying to find the distinct number of debt_code. In my table, I have a column name ad_type. When I use the distinct code, it gives me debt_code with distinct ad_type. So, the debt_code is repeated and I want just the distinct number of debt_code. I am using the below code:

Proc sql;
create table No_AP_SP_address as 
select distinct a.*,
b.ad_type
from Trace_Accounts as a
inner join p2scflow.addressee b on a.debt_code = b.debt_code
where ad_type not in ('AP','SA') 
group by a.debt_code;
quit;

data Test;
infile datalines dsd truncover;
input rep_code debt_code ad_type;
datalines;
122 100001833 DB
122 100001833 XD
168 100003144 DB
168 100003144 XD
168 100003730 DB;
run;
Can you please suggest?

[PaigeMiller · Accepted solution]

In SQL, you can get the distinct values via

proc sql;
     create table distinct_values 
     as select distinct debt_code as distinct_debt_code_values
     from your_dataset;
run;

Or if you just want the number of distinct values, you would use: 

proc sql;
     create table number_distinct_values 
     as select count(distinct debt_code) as distinct_debt_code_values
     from your_dataset;
run;