topic How do I apply different formats when using set operators in proc sql? in SAS Programming

How do I apply different formats when using set operators in proc sql?

CalebSindi — Mon, 02 Oct 2023 09:41:15 GMT

Hi,

I'm trying to apply two formats to my code in proc SQL using the union operator. However, it only applies the first format that I listed. I can't show you actual code for confidentiality reasons however here is a sample code. Column 1 has ordinary numeric values while column 2 has a date format applied to it.

proc sql;
select 'Total of column 1', sum('column 1'n) format=comma18.
from table1
union
select 'Max date of column 2', max('column 2'n) format=worddatx.
from table1;
quit;

Re: How do I apply different formats when using set operators in proc sql?

LinusH — Mon, 02 Oct 2023 09:59:46 GMT

My understanding here is that you are matching two input columns to one output column using UNION, and one column can only have one format at the time.

Usually this is a non-no, but it looks like you are producing a report, and then it's fine.

My suggestion is that you put the numerical value to char column using respective format:

put(sum('column 1'n),comma18)

Re: How do I apply different formats when using set operators in proc sql?

CalebSindi — Mon, 02 Oct 2023 10:35:44 GMT

Thanks for the tip. I managed to crack it by creating macro variables. So it eventually looks something like this

proc sql noprint;
select sum('column 1'n) format=comma18.
into :Total trimmed
from table1;
quit;

proc SQL;
select distinct 'Sum of column 1', "&Total"
from table1;
quit;

Re: How do I apply different formats when using set operators in proc sql?

Tom — Mon, 02 Oct 2023 13:26:30 GMT

Huh?

If the goal was to just make that printout then why bother to make the dataset?

proc sql ;
select sum('column 1'n) label='Sum of column 1' format=comma18. 
from table1;
quit;

Re: How do I apply different formats when using set operators in proc sql?

CalebSindi — Mon, 02 Oct 2023 14:20:58 GMT

The thing is, the printout is key because it will be used as a summary table that gets emailed to some stakeholders monthly.

Re: How do I apply different formats when using set operators in proc sql?

Tom — Mon, 02 Oct 2023 15:43:10 GMT

In that case you will want to generate the report using a reporting procedure like PROC REPORT. Or perhaps even hand crafted using a DATA step. PROC SQL is not a reporting tool.