topic Re: Proc sql in SAS Programming

Proc sql

Iris83 — Tue, 10 May 2016 16:03:08 GMT

PROC SQL ;
create table table1 as
Select /*all the columns*/
T1.date FORMAT=DDMMYY8. ,
T1.bithdaydate FORMAT=DDMMYY8. ,
From work.table t1 ;
Quit ;

That's what i want to produce because my dates are in number format ...
However i want to avoid writting all the column's name .
Thanks for your help !

Regards ,
Iris

Re: Proc sql

Reeza — Tue, 10 May 2016 16:06:39 GMT

You can use T1.* but then you can't specify the date.

If ALL your columns are date and it's a straight select it's probably easier to use a data step.

data table1;
set table;
format _numeric_ ddmmyy8.;
run;

Another little trick I use is the FEEDBACK option. Look at the log after this submission and see the full code expanded so it's easier to modify then.

PROC SQL FEEDBACK ;
create table table1 as
Select t1.*
From work.table t1 ;
Quit ;

Re: Proc sql

Iris83 — Tue, 10 May 2016 16:23:55 GMT

Thank you very much for the tips .

All my column are in different formats ... That's why i can't change all the column's formats into date format .
The t1* as you said is bit working in my situation ...
Regards

Re: Proc sql

PGStats — Tue, 10 May 2016 16:52:03 GMT

Would it be fair to assume that date variables include the word DATE in their name?

Re: Proc sql

Iris83 — Tue, 10 May 2016 17:45:11 GMT

No but , there are only 6 so i don't mind writting them manualy , it's more writting all the other that is complicated .
Thanks for your help

Re: Proc sql

PGStats — Tue, 10 May 2016 18:01:31 GMT

Then go @Reeza's route

data table1;
set table;
format Var1 Var2 Var3 ddmmyy8.;
run;

all other vars will keep their original format (or lack of).