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;
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)
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;
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;
The thing is, the printout is key because it will be used as a summary table that gets emailed to some stakeholders monthly.
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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.