select distinct formatted value in proc sql

[Peter_Y]

Hello:

   I wonder if there is any way in proc sql to select distinct formatted values. For example, the code below formats both 1 and 2 as "Agree". However, the select distinct statement returns two identical lines of "Agree". My assumption is that the distinct keyword applies only to raw value. Is there any generic way to force proc sql operate based on formatted value? By 'generic', what I mean is that I don't want to hand code any format in proc sql. The desired behavior is for proc sql to use whatever format assigned in the dataset for a specific variable. 

Thanks,

Peter

proc format;
value a
1,2='Agree';
run;

data test;
a=1; output; a=2; output;
format a a.;
run;

proc sql;
  select distinct a from test;
quit;

[PaigeMiller]

  select distinct put(a,a.) from test;

[Sajid01]

Format when applied do not impact or have an effect on data storage. They only modify the presentation.

The Proc SQL distinct will apply to what is stored in the dataset and not what is presented.

Thus, your dataset has two distinct values 1 and 2. That is why you see two "Agrees" in the output of your SQL output.