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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: