Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Not applicable
Posts: 0
Accepted Solution

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

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

Accepted Solutions
Solution
‎09-07-2017 02:03 PM
Super Contributor
Super Contributor
Posts: 3,176

Re: Convert $10. to mmddyy10.

Posted in reply to deleted_user
Use this technique and INFORMAT:

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


Scott Barry
SBBWorks, Inc.

View solution in original post


All Replies
Valued Guide
Posts: 2,191

Re: Convert $10. to mmddyy10.

Posted in reply to deleted_user
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
Not applicable
Posts: 0

Re: Convert $10. to mmddyy10.

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.....
)
Respected Advisor
Posts: 4,736

Re: Convert $10. to mmddyy10.

Posted in reply to deleted_user
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;
Not applicable
Posts: 0

Re: Convert $10. to mmddyy10.

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?
Not applicable
Posts: 0

Re: Convert $10. to mmddyy10.

Posted in reply to deleted_user
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?!
Not applicable
Posts: 0

Re: Convert $10. to mmddyy10.

Posted in reply to deleted_user
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
Solution
‎09-07-2017 02:03 PM
Super Contributor
Super Contributor
Posts: 3,176

Re: Convert $10. to mmddyy10.

Posted in reply to deleted_user
Use this technique and INFORMAT:

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


Scott Barry
SBBWorks, Inc.
Not applicable
Posts: 0

Re: Convert $10. to mmddyy10.

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

Thanks!
🔒 This topic is solved and locked.

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

Discussion stats
  • 8 replies
  • 11752 views
  • 0 likes
  • 4 in conversation