BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
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
1 ACCEPTED SOLUTION

Accepted Solutions
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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

8 REPLIES 8
Peter_C
Rhodochrosite | Level 12
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
deleted_user
Not applicable
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.....
)
Patrick
Opal | Level 21
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;
deleted_user
Not applicable
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?
deleted_user
Not applicable
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?!
deleted_user
Not applicable
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Use this technique and INFORMAT:

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


Scott Barry
SBBWorks, Inc.
deleted_user
Not applicable
This is just what I was looking for - I didn't know about anydtdte10. format.

Thanks!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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