How to format a variable in proc sql without specify its name in select statement

[GeorgeSAS]

hello everyone,

Does anyone know how I can format a variable in proc sql without specify its name in select statement?(I know I can do it if i specify its name:     proc sql; select a $18,b,c from ......,but i want use * to choose all variables here)

Here is the problem code:

proc sql;

create table need as

select *  format a =$18. from one

group by b,c

order by a;

quit;

Thanks

[ballardw]

If it is only one variable you might be able to use an ALTER TABLE statement on the created output in a separate bit of code.
after created table need:

alter table need modify a format=$18.;

[jwillis · Accepted solution]

You can code:

proc sql;

create table need as

select zz.*,

          af = zz.a  format = $18.

from one zz

group by b,c

order by af;

quit;

Depending on your situation, you will have to delete a then rename af if your program is looking for a later on.  You should also need to trim zz.a if a's length is longer than 18 characters.

[Reeza]

I'd use proc datasets afterwards to apply the format instead of fiddling with the SQL.

Assuming you're too lazy to type out all the variables you can use

PROC SQL feedback;

rest of code;

quit;

check the log and it will list all the variables and you can modify the ones you want.

If you're using * to select all because you expect variables to change in the future this method won't work.

[GeorgeSAS]

proc format;

value fmt

1-12='young'

other='old';run;

proc sql;

create table need as

select zz.*,

          zz.age as af  format = fmt.

from sashelp.class as zz;

quit;

proc datasets nolist;

   modify need;

      rename age=age2;

      format age2 fmt.;

quit;

[GeorgeSAS]

thanks,some revise:

proc sql;

create table need as

select zz.*,

         zz.a as af  format = $18.

from one zz

group by b,c

order by af;

quit;