Hi Group,

I am selecting a number of variables(50 +) and using Select * in a SQL query
as follows.

Proc sql;

select *
from abc.xyz;

quit;

What if I had to enhance my output by changing format or lablel of only one
or two variables. When we select variables by mentioning column names in the
select clause, we can use format= option right after the column name. But
when we use select * how can we define or modify the format of a particular
variable. Can anyone give any idea?

Thanks

Sanjay

Sanjay,

If you don't mind the warning that will appear in your log, you could use
something like the following:

data have;
  input a b c d;
  cards;
1 2 3 4
5 6 7 8
;
run;

proc sql;
  create table want as
    select c format=8.3, *
     from have;
quit;

HTH,
Art
--------
On Sat, 28 Jul 2007 15:45:53 -0400, sanjay sun <sanjaysum@GMAIL.COM> wrote:

>Hi Group,
>
>I am selecting a number of variables(50 +) and using Select * in a SQL
query
>as follows.
>
>Proc sql;
>
>select *
>from abc.xyz;
>
>quit;
>
>What if I had to enhance my output by changing format or lablel of only
one
>or two variables. When we select variables by mentioning column names in
the
>select clause, we can use format= option right after the column name. But
>when we use select * how can we define or modify the format of a
particular
>variable. Can anyone give any idea?
>
>Thanks
>
>Sanjay

You could use a separate ALTER statement.

Otherwise, I don't think there's a way to do what you want in pure SQL,
other than by listing all the variables individually.

You could also do a join, if the table has a unique key, but it's icky
and gives warnings:

=====
proc sql;

    create table new as
       select   a.*,
                b.age format=roman10.,
                c.*
       from     sashelp.class (keep=name--sex) as a,
                sashelp.class (keep=name age) as b,
                sashelp.class (keep=name height--weight) as c
       where    a.name = b.name
                and b.name = c.name
                and a.name = c.name
       order    by name;
quit;

proc compare base=sashelp.class compare=new;
run;
=====

Is there a way in SQL to join tables that emulates data step MERGE
without BY?


sanjay sun wrote:
> Hi Group,
>
> I am selecting a number of variables(50 +) and using Select * in a SQL query
> as follows.
>
> Proc sql;
>
> select *
> from abc.xyz;
>
> quit;
>
> What if I had to enhance my output by changing format or lablel of only one
> or two variables. When we select variables by mentioning column names in the
> select clause, we can use format= option right after the column name. But
> when we use select * how can we define or modify the format of a particular
> variable. Can anyone give any idea?
>
> Thanks
>
> Sanjay

There are lots of things about SQL which discourage wide tables.

On Sat, 28 Jul 2007 15:45:53 -0400, sanjay sun <sanjaysum@GMAIL.COM> wrote:

>Hi Group,
>
>I am selecting a number of variables(50 +) and using Select * in a SQL query
>as follows.
>
>Proc sql;
>
>select *
>from abc.xyz;
>
>quit;
>
>What if I had to enhance my output by changing format or lablel of only one
>or two variables. When we select variables by mentioning column names in the
>select clause, we can use format= option right after the column name. But
>when we use select * how can we define or modify the format of a particular
>variable. Can anyone give any idea?
>
>Thanks
>
>Sanjay