Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 241
Accepted Solution

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

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


Accepted Solutions
Solution
‎03-30-2015 02:49 PM
Regular Contributor
Posts: 217

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

Posted in reply to GeorgeSAS

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.

View solution in original post


All Replies
Super User
Posts: 11,343

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

Posted in reply to GeorgeSAS

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

Solution
‎03-30-2015 02:49 PM
Regular Contributor
Posts: 217

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

Posted in reply to GeorgeSAS

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.

Super User
Posts: 19,877

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

Posted in reply to GeorgeSAS

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.

Regular Contributor
Posts: 241

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

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;

Regular Contributor
Posts: 241

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

Posted in reply to 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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 20825 views
  • 4 likes
  • 4 in conversation