BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
GeorgeSAS
Lapis Lazuli | Level 10

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

1 ACCEPTED SOLUTION

Accepted Solutions
jwillis
Quartz | Level 8

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

5 REPLIES 5
ballardw
Super User

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
Quartz | Level 8

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
Super User

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
Lapis Lazuli | Level 10

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
Lapis Lazuli | Level 10

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 190925 views
  • 8 likes
  • 4 in conversation