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
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.
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.;
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.
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.
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.