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
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.
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 looks like this as attached
This cannot be in SAS. To have both display methods for dates in a single column, the column must be character. Period.
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.
No, It is in sas. i have not used csv
Do a proc contents on the data set and a proc print with obs=50, then post the results.
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.
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!
Yes, i have merged 2 datasets one has date format 20150912 and other with date9.
@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.
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
@Kurt_Bremser: Have a look at the dataset in VIEWTABLE.
Heh.
Rarely use that any longer, as it requires starting SAS in DM mode in X-Windows directly on the server.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.