DATA Step, Macro, Functions and more

Date format

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 115
Accepted Solution

Date format

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


Accepted Solutions
Solution
‎01-21-2016 08:22 AM
Trusted Advisor
Posts: 1,115

Re: Date format

[ Edited ]

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.

View solution in original post


All Replies
Super User
Posts: 5,260

Re: Date format

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().

Data never sleeps
Frequent Contributor
Posts: 115

Re: Date format

data looks like this as attached


Capture.JPG
Super User
Posts: 6,963

Re: Date format

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,413

Re: Date format

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.

Frequent Contributor
Posts: 115

Re: Date format

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

Super User
Posts: 6,963

Re: Date format

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 115

Re: Date format


Capture.JPG
Solution
‎01-21-2016 08:22 AM
Trusted Advisor
Posts: 1,115

Re: Date format

[ Edited ]

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.

Trusted Advisor
Posts: 1,115

Re: Date format

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!

Frequent Contributor
Posts: 115

Re: Date format

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

Super User
Posts: 10,538

Re: Date format


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.

Super User
Posts: 6,963

Re: Date format

[ Edited ]

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

 

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Trusted Advisor
Posts: 1,115

Re: Date format

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

Super User
Posts: 6,963

Re: Date format

Heh.

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is solved.

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

Discussion stats
  • 17 replies
  • 476 views
  • 0 likes
  • 7 in conversation