BookmarkSubscribeRSS Feed
CalebSindi
SAS Employee

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;
5 REPLIES 5
LinusH
Tourmaline | Level 20

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)
Data never sleeps
CalebSindi
SAS Employee

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
Super User Tom
Super User

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
SAS Employee

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

Tom
Super User Tom
Super User

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.

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1301 views
  • 0 likes
  • 3 in conversation