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

[CalebSindi]

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;

[LinusH]

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)

[CalebSindi]

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;

[Tom]

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;

[CalebSindi]

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

[Tom]

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.