topic Re: How to format a variable in proc sql without specify its name in select statement in SAS Procedures

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

GeorgeSAS — Mon, 30 Mar 2015 15:54:40 GMT

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

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

ballardw — Mon, 30 Mar 2015 16:02:59 GMT

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.;

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

jwillis — Mon, 30 Mar 2015 18:49:57 GMT

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.

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

Reeza — Mon, 30 Mar 2015 18:56:39 GMT

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.

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

GeorgeSAS — Sat, 04 Apr 2015 17:22:13 GMT

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;

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

GeorgeSAS — Sat, 04 Apr 2015 17:30:59 GMT

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;