convert text date to DD/MM/YYYY from DDMMMYYYY

[Bellefeuille]

i have code that the output is not cooperating with my sql programs.

I use sas to extract form a grid, so downloading the table at once is not idea. 

my code looks like this

the open_dt has an output of DDMMMYYYY

and i have to do tons of converting which i would perfer do solve while doing the extract.

is this possible to add to the select satement?

%sastable(one.Cust_To_Acct_To_Trans3,
select DISTINCT
cu.cust_no
,ac.acct_no

,open_dt

*------ Below arent working......................

,input(ac.open_dt,date9.) as opendate format=date9.
,input(ac.open_dt, yymmn6.) as date_c format=yymmdd6.

[PeterClemmensen]

What does your log say? And what does your data look like?

[Bellefeuille]

sas log shows

ERROR: CLI describe error: [IBM][CLI Driver][DB2/AIX64] SQL0104N An unexpected token ")" was
found following "ut(ac.open_dt,date9.". Expected tokens may include: "<identifier>".
SQLSTATE=42601

the datalooks like

02OCT2017

[Bellefeuille]

I tried

creating a new dataset with the table,

this seemd to create an empty column.

Data one.want;
set one.Cust_To_Acct_To_Trans3;
OpenDate = input(open_dt, date9.);
format OpenDate date9. ; /* or any of the other date formats humans like*/
run;
endRsubmit;

[ballardw]

---

@Bellefeuille wrote:

I tried

creating a new dataset with the table,

this seemd to create an empty column.

Data one.want;
set one.Cust_To_Acct_To_Trans3;
OpenDate = input(open_dt, date9.);
format OpenDate date9. ; /* or any of the other date formats humans like*/
run;
endRsubmit;

---

depending on a number of things you may have some beginning blank spaces in your open_dt variable. See this example:

data example;
   x='01JAN2017';
   y=' 01JAN2017';
   xdate= input(x,date9.);
   ydate= input(y,date9.);
   format xdate ydate date9.;
run;

Attempting to convert y fails but generates

NOTE: Invalid argument to function INPUT at line 2894 column 11.
x=01JAN2017 y=01JAN2017 xdate=01JAN2017 ydate=. _ERROR_=1 _N_=1
NOTE: Mathematical operations could not be performed at the following places. The results of the
      operations have been set to missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1 at 2894:11

The line and column would most certainly vary for your code but any conversion like would fail.

Other possible (hopefully remote) content such as imbedded blanks would generate similar warning. Also anything where the day is not valid for the month or the month portion is a different three-letter abbreviation than SAS expects due to National Language settings might be an issue.

Show some example values that didn't covert and the log when using them.

[Tom]

I have no idea what a "grid" is in this context.  But what type of variable do you get if you just select it without trying to convert it?

Perhaps you already have a date variable and you just want to attach a different format?

,ac.open_dt as opendate format=mmddyy10.

Personally I wouldn't use MDY or DMY ordering because of the potential for confusing. So either stick with the DATE9 format you are currently seeing or use YYMMDD10. format so that the data is YMD order and you don't confuse your UK friends.

[Bellefeuille]

its date

i guess what im trying to do is export it so i can use it in sql

Table - ACCT DATE

Again when i export the content,  

the date is exported as DDMMMYYYY

and if i load the data in sql afterwards, it will concider it txt because of the MMM

[Tom · Accepted solution]

---

@Bellefeuille wrote:

its date

i guess what im trying to do is export it so i can use it in sql

Table - ACCT DATE

Again when i export the content,  

the date is exported as DDMMMYYYY

and if i load the data in sql afterwards, it will concider it txt because of the MMM

---

What do you mean by SQL? 

In SAS code you can just use a date literal, which uses the DATE9 format that you already have. Whether you do it in normal SAS code or SQL code that you are using in PROC SQL.

where acct_date = '17NOV2017'd

If some other flavor of SQL then you will need specify what flavor and what syntax it uses to code date literals.  In Teradata you you can use the DATE keyword to indicate that you have a date literal.

where acct_date = date '2017-11-17'

If you are pushing the data into a database then what format you have attached to it should not change how it gets stored in the remote database.  SAS will see that it is a DATE and create the proper variable type in the remote database.

libname out teradata server= .... ;
data out.newtable;
  set work.mytable;
run;