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;
select distinct put(a,a.) from test;
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.
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.