topic How do I format a variable as % and comma10. in same column using proc sql? in SAS Programming

How do I format a variable as % and comma10. in same column using proc sql?

SASGeek — Fri, 06 Dec 2024 22:56:48 GMT

Hello all,

I have a client that INSISTS that EVERYTHING be done in PROC SQL. So I need the following.

I have output that has both comma and numeric formatting in the same column. The client wants the results in a dataset (again, don't ask).

The pos positions 1 & 2 need to be comma10. format, and pos 3 needs to be nlpcti10.2

descr	col 1	pos
Total	123,456	1
Change	123	2
% Change	45%	3

CAN that be done in PROC SQL? At least I have the flags so only pos = 3 needs the percent format, pos = 1, 2 need the comma10.

I tried using a CASE WHEN but it doesn't work. If I have to do it in a DATA step, OK, but they won't allow a proc format.

I have tried it in PROC REPORT but the output dataset doesn't hold the formatting nor the new variable names.

HELP PLEASE! I'M DESPERATE

Thank you

Re: How do I format a variable as % and comma10. in same column using proc sql?

Kurt_Bremser — Fri, 06 Dec 2024 23:14:01 GMT

No, it cannot be done with numeric variables. The format is a variable (or column) attribute and is the same throughout the dataset. You can only do this in a report, or by storing the formatted values in a character variable.

Re: How do I format a variable as % and comma10. in same column using proc sql?

PaigeMiller — Sat, 07 Dec 2024 00:02:46 GMT

If you want a SAS data set with different formats on each row, I agree with @Kurt_Bremser it can't be done.

However, if you want a REPORT, then it is possible in PROC REPORT. But of course this violates the "do everything in PROC SQL" rule and also that you want the results as a SAS dataset.

So if you're desperate, I'm afraid you are going to remain desperate. Perhaps you can convince your client that sometimes SQL can't do everything.

You could use character variables, but of course then you can't perform arithmetic (like addition) or finding maximums from these character variables. (And then, it sounds like you want a report, not a data set)

Re: How do I format a variable as % and comma10. in same column using proc sql?

Tom — Sat, 07 Dec 2024 00:05:32 GMT

Make the COL1 variable (what you called a COLUMN) a CHARACTER string. Then you can store anything you want in it.

So let's assume the data is currently in a dataset named HAVE with variables named DESCR, POS and NUMVAR. Then you could use SQL like this:

select descr
    , case when (pos=3) then put(numvar,percent6.) else put(numvar,comma10.) end as COL1
    , pos
from HAVE

Re: How do I format a variable as % and comma10. in same column using proc sql?

mkeintz — Sat, 07 Dec 2024 04:32:53 GMT

First, I agree that this problem is best be solved in a PROC REPORT.

But if you must somehow only use SQL, you would have to place a constraint on subranges of the column. For instance, if you choose to only use values of -1 through +1 to represent -100% through 100%, and use only other values to be presented in comma format, you could do this.

Take the sashelp.demographics dataset, with variables POP (actual population count) and POPURBAN (percentage represented as zero through one), put into the same column.

proc format;
  value pc  -1 - 1=[percentn10.2]
            other=[comma10.0];
run;

proc sql;
  create table want as 
  select name, 1 as row, pop as column_var format=pc10.2 from sashelp.demographics
  union
  select name, 2 as row, popurban as column_var  format=pc10.2 from sashelp.demographics
  order by name, calculated row
  ;
quit;

Re: How do I format a variable as % and comma10. in same column using proc sql?

Quentin — Sat, 07 Dec 2024 16:22:07 GMT

Can you show the input dataset you have?

The goal is to make an output dataset with three columns and three rows?

Can we assume that the Col1 variable is a character variable?

Re: How do I format a variable as % and comma10. in same column using proc sql?

SASGeek — Sat, 07 Dec 2024 17:28:33 GMT

Thank you Tom. Brilliant. It doesn't HAVE to result in numeric output, just it has to look numeric. BRILLIANT.

Thank you