topic Re: select distinct formatted value in proc sql in SAS Programming

select distinct formatted value in proc sql

Peter_Y — Fri, 29 Apr 2022 12:36:26 GMT

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;

Re: select distinct formatted value in proc sql

PaigeMiller — Fri, 29 Apr 2022 12:40:31 GMT

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

Re: select distinct formatted value in proc sql

Sajid01 — Mon, 02 May 2022 14:40:53 GMT

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.