topic Re: Convert $10. to mmddyy10. in SAS Procedures

Convert $10. with value of yyyy-mm-dd to display as mmddyy10.

deleted_user — Wed, 27 Aug 2008 20:33:36 GMT

I need to convert a result column from a database search from character $10. format to display in a report as mm/dd/yyyy format. Message was edited by: jcakers

Re: Convert $10. to mmddyy10.

Peter_C — Wed, 27 Aug 2008 21:25:01 GMT

how do you get this information out of your database?
It may be possible for that platform to make the conversion in a way that a SAS/Access connection will recognise the information as a date.

hth

PeterC

Re: Convert $10. to mmddyy10.

Patrick — Wed, 27 Aug 2008 23:24:50 GMT

Use an informat to convert the character string to a SAS date and use a format to write the SAS date in the form you want to.
Example:
data _null_;
datestring='20080828';
date=input(datestring,yymmdd10.);
put date= ddmmyy10.;
run;

Re: Convert $10. to mmddyy10.

deleted_user — Thu, 28 Aug 2008 14:51:06 GMT

I tried that, and here's the log result:

MPRINT(QLA025): start_date=input(PERIOD_START_DT,yymmdd10.);
MPRINT(QLA025): put start_date= ddmmyy10.;
MPRINT(QLA025): end_date=input(PERIOD_END_DT,yymmdd10.);
MPRINT(QLA025): put end_date= ddmmyy10.;
MPRINT(QLA025): set displayDataSet;
ERROR: Variable PERIOD_START_DT has been defined as both character and numeric.
ERROR: Variable PERIOD_END_DT has been defined as both character and numeric.

along with:
WARNING: The data set WORK.DISPLAYDATASET may be incomplete. When this step was stopped there
were 0 observations and 27 variables.
WARNING: Data set WORK.DISPLAYDATASET was not replaced because this step was stopped.

The variables PERIOD_START_DT and PERIOD_END_DT are defined in the SQL result table as $10. with values either spaces or like 2008-12-31 (yes, with the dashes in the field).

So how do I get it to re-format and display the field as mm/dd/yyyy?

Re: Convert $10. to mmddyy10.

deleted_user — Thu, 28 Aug 2008 14:53:26 GMT

The data is retrieved with a PROC SQL in a separate macro, and results are placed into a work table.

Eg:
proc sql noprint;
connect to db2 as db2(dsn=&dsource uid=&uID pwd=&pwd );
create table PENDING_APPS as
select * from connection to db2
(
SELECT.....FROM.....WHERE.....
)

Re: Convert $10. to mmddyy10.

deleted_user — Thu, 28 Aug 2008 15:49:18 GMT

I've just tried the sledgehammer method, and got the same error:

MPRINT(QLA025):
start_date=substr(PROCESSING_START_DT,6,2)||'/'||substr(PROCESSING_START_DT,9,2)||'/'||substr(PROCESSING_START_DT,1,4);
MPRINT(QLA025):
end_date=substr(PROCESSING_END_DT,6,2)||'/'||substr(PROCESSING_END_DT,9,2)||'/'||substr(PROCESSING_
END_DT,1,4);
MPRINT(QLA025): set displayDataSet;
ERROR: Variable PROCESSING_START_DT has been defined as both character and numeric.
ERROR: Variable PROCESSING_END_DT has been defined as both character and numeric.

I'm not applying any formatting to the fields, anywhere. How does something get defined as both character and numeric?!

Re: Convert $10. to mmddyy10.

deleted_user — Thu, 28 Aug 2008 16:24:52 GMT

Ok, the sledgehammer way works - when you use the same field names of start_date and end_date in the DATA step as the PROC REPORT step. My error was the PROC REPORT step used start_dt and end_dt.

So I don't know why the error showed on the other fields.

But if anyone has a better way than to SUBSTR, I'd love to hear it. Message was edited by: jcakers

Re: Convert $10. to mmddyy10.

sbb — Thu, 28 Aug 2008 18:10:40 GMT

Use this technique and INFORMAT:

data _null_;
dt = input("2008/10/10",anydtdte10.);
put dt= mmddyy10.;
run;

Scott Barry
SBBWorks, Inc.

Re: Convert $10. to mmddyy10.

deleted_user — Thu, 28 Aug 2008 18:44:27 GMT

This is just what I was looking for - I didn't know about anydtdte10. format.

Thanks!