Help using Base SAS procedures

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

Accepted Solution Solved
Reply
N/A
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,174

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,177

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
N/A
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,173

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;
N/A
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?
N/A
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?!
N/A
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,174

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.
N/A
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.

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

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