proc sql pass-thru and format option

[azaufan]

I'm using proc sql to connect to db2 and want to apply a date format
to a created column ...below is the example...DB2 doesn't seem to like
the format option no matter where i put it. Can this be fixed?

EXAMPLE 1:
proc sql;
connect to db2 (database=XXXXX ril=ur);
create table lib1.table as
select *
from connection to db2
(select a,
b,
c (format date9.)
from tablename
group by a,b,c
order by a,b,c
);
quit;
run;

OR

proc sql;
connect to db2 (database=XXXXX ril=ur);
create table lib1.table (c format=date9.) as
select *
from connection to db2
(select a,
b,
c
from tablename
group by a,b,c
order by a,b,c
);
quit;
run;

[toby dunn]

Been a long time since I had to play with a DB2 but try:

Proc SQL ;
Connect To DB2 ( DataBase = XXXXX Ril = UR ) ;
Create Table Lib1.Table As
Select A , B , C Format = Date9.
From Connection To DB2
( Select A , B , C
From TableName )
Order By A , B , C ;
Quit ;





Toby Dunn

To sensible men, every day is a day of reckoning. ~John W. Gardner

The important thing is this: To be able at any moment to sacrifice that
which we are for what we could become. ~Charles DuBois

Don't get your knickers in a knot. Nothing is solved and it just makes you
walk funny. ~Kathryn Carpenter






From: azaufan <***@GMAIL.COM>
Reply-To: azaufan <***@GMAIL.COM>
To: SAS-***@LISTSERV.UGA.EDU
Subject: proc sql pass-thru and format option
Date: Tue, 3 Apr 2007 09:33:37 -0700

I'm using proc sql to connect to db2 and want to apply a date format
to a created column ...below is the example...DB2 doesn't seem to like
the format option no matter where i put it. Can this be fixed?

EXAMPLE 1:
proc sql;
connect to db2 (database=XXXXX ril=ur);
create table lib1.table as
select *
from connection to db2
(select a,
b,
c (format date9.)
from tablename
group by a,b,c
order by a,b,c
);
quit;
run;

OR

proc sql;
connect to db2 (database=XXXXX ril=ur);
create table lib1.table (c format=date9.) as
select *
from connection to db2
(select a,
b,
c
from tablename
group by a,b,c
order by a,b,c
);
quit;
run;

_________________________________________________________________
Exercise your brain! Try Flexicon.
http://games.msn.com/en/flexicon/default.htm?icid=flexicon_hmemailtaglineapril07

[Pardee, Roy]

You're going to have to apply the format in a second datastep, on the
sas side. DB2 doesn't know anything about sas formats.

You'll probably also have to use the datepart() function to extract just
the date from the datetime field that DB2 will almost certainly give
you.

HTH,

-Roy

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of
azaufan
Sent: Tuesday, April 03, 2007 9:34 AM
To: SAS-***@LISTSERV.UGA.EDU
Subject: proc sql pass-thru and format option

I'm using proc sql to connect to db2 and want to apply a date format to
a created column ...below is the example...DB2 doesn't seem to like the
format option no matter where i put it. Can this be fixed?

EXAMPLE 1:
proc sql;
connect to db2 (database=XXXXX ril=ur);
create table lib1.table as
select *
from connection to db2
(select a,
b,
c (format date9.)
from tablename
group by a,b,c
order by a,b,c
);
quit;
run;

OR

proc sql;
connect to db2 (database=XXXXX ril=ur);
create table lib1.table (c format=date9.) as select * from connection to
db2 (select a,
b,
c
from tablename
group by a,b,c
order by a,b,c
);
quit;
run;

[Sigurd Hermansen]

Toby has the correct syntax. I'll simply mention that any syntax or
semantics peculiar to SAS SQL, including but not limited to SAS formats,
cannot appear in text being passed thru to the host database system;
that is, between '...connection to <DMBS> (' and a closing parentheses,
')'. This rule excludes SAS macrovariables or Macros that resolve to
appropriate text.
S

-----Original Message-----
From: owner-sas-***@listserv.uga.edu [mailto:owner-sas-***@listserv.uga.edu]
On Behalf Of azaufan
Sent: Tuesday, April 03, 2007 12:34 PM
To: sas-***@uga.edu
Subject: proc sql pass-thru and format option


I'm using proc sql to connect to db2 and want to apply a date format to
a created column ...below is the example...DB2 doesn't seem to like the
format option no matter where i put it. Can this be fixed?

EXAMPLE 1:
proc sql;
connect to db2 (database=XXXXX ril=ur);
create table lib1.table as
select *
from connection to db2
(select a,
b,
c (format date9.)
from tablename
group by a,b,c
order by a,b,c
);
quit;
run;

OR

proc sql;
connect to db2 (database=XXXXX ril=ur);
create table lib1.table (c format=date9.) as
select *
from connection to db2
(select a,
b,
c
from tablename
group by a,b,c
order by a,b,c
);
quit;
run;