BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vraj1
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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

17 REPLIES 17
LinusH
Tourmaline | Level 20

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
vraj1
Quartz | Level 8

data looks like this as attached


Capture.JPG
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

vraj1
Quartz | Level 8

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

vraj1
Quartz | Level 8

Capture.JPG
FreelanceReinh
Jade | Level 19

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.

FreelanceReinh
Jade | Level 19

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!

vraj1
Quartz | Level 8

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

ballardw
Super User

@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.

Kurt_Bremser
Super User

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

 

 

FreelanceReinh
Jade | Level 19

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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 17 replies
  • 4419 views
  • 0 likes
  • 7 in conversation