topic Re: Date format in SAS Programming

Date format

vraj1 — Thu, 21 Jan 2016 11:54:29 GMT

In my dataset i have a start date with both 20150912 and 22NOV2015 formats.

How do i convertthem into one format like 12OCT2015.

The variable is in NUM and format as date9. and informat 9. with length as 4

Re: Date format

LinusH — Thu, 21 Jan 2016 12:14:54 GMT

Hm, do you say that you have date9. format and it still shows like YYYYMMDD? Shouldn't be possible.

Please clarify with actual data (non-formatted).

To convert a data from a numerical value you need to nest input() and put().

Re: Date format

vraj1 — Thu, 21 Jan 2016 12:21:32 GMT

data looks like this as attached

Re: Date format

Kurt_Bremser — Thu, 21 Jan 2016 12:27:52 GMT

This cannot be in SAS. To have both display methods for dates in a single column, the column must be character. Period.

Re: Date format

RW9 — Thu, 21 Jan 2016 12:34:49 GMT

That is an Excel file correct? You could export it to CSV and use informat anydate. That *might* work. However you have hit one of the big downsides to using Excel for any purpose. It is not a database, it has no strucutre, hence you have columns with different data types. Simple solution don't use Excel. Here are some Excel specific answers:

http://fiveminutelessons.com/learn-microsoft-excel/convert-text-value-date-excel

https://www.ablebits.com/office-addins-blog/2015/03/26/excel-convert-text-date/

Or, save as CSV (always move to CSV and write your own import program!) read that value in as character then post process the text into a date.

Re: Date format

FreelanceReinh — Thu, 21 Jan 2016 12:50:36 GMT

I think you are in trouble, unless you have access to the original data before they were read into a numeric variable of length 4.

It looks like you have indeed numeric values like 20151216, i.e. of the form YYYYMMDD. However, length 4 bytes is insufficient for integers greater than 2097152 (but sufficient for proper SAS date values). Did you notice that your YYYYMMDD values are rounded to multiples of 16? This is a logical consequence of the insufficient length. It leads to invalid values such as 20160096, but also the majority of valid values like 20151216 will most likely be wrong!

Re: Date format

vraj1 — Thu, 21 Jan 2016 12:50:58 GMT

No, It is in sas. i have not used csv

Re: Date format

Kurt_Bremser — Thu, 21 Jan 2016 12:54:18 GMT

Do a proc contents on the data set and a proc print with obs=50, then post the results.

Re: Date format

vraj1 — Thu, 21 Jan 2016 12:54:51 GMT

Yes, i have merged 2 datasets one has date format 20150912 and other with date9.

Re: Date format

vraj1 — Thu, 21 Jan 2016 12:58:46 GMT

Re: Date format

Kurt_Bremser — Thu, 21 Jan 2016 13:17:08 GMT

When I try to replicate this (setting a date-formatted variable to a YYYYMMDD value), then non-valid numbers are displayed as stars instead of the value. There must be something more at work here for SAS to use the DATE9. and the basic number format, IMO.

This code:

data test;
length date 4;
format date date9.;
date = 20160096;
output;
date=today();
output;
run;
proc print;run;

yields this output:

Obs date
1 *********
2 21JAN2016

Re: Date format

FreelanceReinh — Thu, 21 Jan 2016 13:20:00 GMT

You should convert the YYYYMMDD values to SAS date values before they are stored in a variable of length 4.

Example:

data old;
d=20151231;
run;

data new;
set old;
d=input(put(d,8.),yymmdd8.);
run;

To do this, you can either prepare a modified copy of the dataset with YYYYMMDD dates (see example above) and use this in the merge step (that's the easier way), or you can perform the date conversion in the merge step itself, at least if the affected date variable is not involved in the BY statement.

Re: Date format

FreelanceReinh — Thu, 21 Jan 2016 13:22:38 GMT

@Kurt_Bremser: Have a look at the dataset in VIEWTABLE.

Re: Date format

Kurt_Bremser — Thu, 21 Jan 2016 13:27:13 GMT

Heh.

Rarely use that any longer, as it requires starting SAS in DM mode in X-Windows directly on the server.

Re: Date format

FreelanceReinh — Thu, 21 Jan 2016 13:38:30 GMT

I don't use VIEWTABLE either (for several reasons). I've noticed, with the "mixed" date values it writes error messages to the log (in black color!), one for each invalid value:

ERROR: There was a problem with the format so BEST. was used.

So, the table itself shows a mixture of BEST. and DATE9. formatted values, as shown in @vraj1's screenshot.

Re: Date format

ballardw — Thu, 21 Jan 2016 16:34:34 GMT

@vraj1 wrote:

Yes, i have merged 2 datasets one has date format 20150912 and other with date9.

You may want to go back and remerge those data sets. Add a length statement setting the length to 8 before the SET or MERGE statement used.

Re: Date format

moetimollo — Tue, 19 Jul 2016 08:07:59 GMT

Hi,

Is it possible with SAS to read a date from Oracle Database and write it out as format shown below.

30Apr2016:00:00:00

Thanks

Re: Date format

RW9 — Tue, 19 Jul 2016 09:21:22 GMT

Hi,

Please don't reopen old posts which have been closed. You have a post here:

https://communities.sas.com/t5/General-SAS-Programming/Date-format-with-the-specific-format-30Apr2016-00-00-00/m-p/285453

About this.