DATA Step, Macro, Functions and more

convert text date to DD/MM/YYYY from DDMMMYYYY

Accepted Solution Solved
Reply
Contributor
Posts: 21
Accepted Solution

convert text date to DD/MM/YYYY from DDMMMYYYY

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.


Accepted Solutions
Solution
‎11-17-2017 10:47 PM
Super User
Super User
Posts: 7,938

Re: convert text date to DD/MM/YYYY from DDMMMYYYY

Posted in reply to Bellefeuille

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;

View solution in original post


All Replies
PROC Star
Posts: 1,218

Re: convert text date to DD/MM/YYYY from DDMMMYYYY

Posted in reply to Bellefeuille

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

Contributor
Posts: 21

Re: convert text date to DD/MM/YYYY from DDMMMYYYY

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

Contributor
Posts: 21

Re: convert text date to DD/MM/YYYY from DDMMMYYYY

Posted in reply to 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;

Super User
Posts: 13,347

Re: convert text date to DD/MM/YYYY from DDMMMYYYY

Posted in reply to Bellefeuille

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.

Super User
Super User
Posts: 7,938

Re: convert text date to DD/MM/YYYY from DDMMMYYYY

Posted in reply to Bellefeuille

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.

Contributor
Posts: 21

Re: convert text date to DD/MM/YYYY from DDMMMYYYY

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

Solution
‎11-17-2017 10:47 PM
Super User
Super User
Posts: 7,938

Re: convert text date to DD/MM/YYYY from DDMMMYYYY

Posted in reply to Bellefeuille

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 1059 views
  • 0 likes
  • 4 in conversation