Hi
I have a date column that looks like this
1106
1209
0101
0102
i need the data to look like this
11/06/14
12/09/14
01/01/15
01/02/15
i used this code in 2014 an was hoping for any insight how to make it work with 2014 and 2015 and any future years
data table1;
set table;
newdate =mdy (substr(old date ,1,2),substr(old date,3,4),2014);
format newdate mmddyy9.;
run;
You have to correct the name of the variable old date which is invalid. It shoudt be something like old_date, olddate or "old date"n. Check the input dataset table.
CTorres
Try this:
data have;
input olddate $;
cards;
1106
1209
0101
0102
;
run;
data want (drop=year);
set have;
if substr(olddate,1,2)>'10' then year=2014;
else year=2015;
newdate =mdy (substr(olddate ,1,2),substr(olddate,3,4),year);
format newdate mmddyy9.;
run;
CTorres
To expand on what you supplied, if they want to update these values for the current year, they can use the year function to extract the current year:
data have;
input olddate $;
cards;
1106
1209
0101
0102
;
run;
data want;
set have;
newdate =mdy(input(substr(olddate,1,2),2.),input(substr(olddate,3,4),2.),year(today()));
format newdate mmddyy9.;
run;
How do you know what year goes with what record?
If this is actual work product I would mention that a lack of a year value in the data is just asking for all kinds of trouble at some point.
Suppose at some time in the future you (or someone else) have to go back and recreate data from an archive due to some loss. The question about year gets much more complicated then.
Assuming that the records are in ascending order according to their date (and that you don't have records with month=12 and year=2014 followed by month=12 and year=2015 or so) :
Data Have;
Input Date $;
Datalines;
1106
1209
0101
0102
;
Run;
Data Want (Keep=Date_new);
Set Have;
Retain Year 2014 Prev_Month;
If _N_ eq 1 Then Prev_Month=Input(Substr(Date,3,2),2.);
Else If Input(Substr(Date,3,2),2.) < Prev_Month Then Year+1;
Prev_Month=Input(Substr(Date,3,2),2.);
Date_new=MDY(Put(Substr(Date,3,2),2.),Put(Substr(Date,1,2),2.),Year);
Format Date_new DDMMYY8.;
Run;
Yes with out year it's diffcult to get it thanks for the assistance
It is not clear whether you are asking how to convert current data in this format or historical data.
For example the Unix ls -l command will put out dates with month and day and then either the year when the files are more than a year old or the time for recent files. So you have to calculate the year based on what time you generated the list.
Also it is not clear from your examples if your values represent MMDD or DDMM.
Either way you do not want to compound the problem by generating a string that only has two digits for the year.
So assuming that you already have a numeric variable DATE with values in the form MMDD here is one way to convert the values to an actual date value. Or to convert it to a string that has slashes in it.
data have ;
input date @@;
cards;
1116 1209 0101 0102
;;;
data want ;
set have ;
year=year(today());
month = int(date/100);
day = mod(date,100);
if mdy(month,day,year) > today() then year=year-1;
date2 = mdy(month,day,year);
date2char = put(date2,mmddyys10.);
format date2 date9.;
put (_all_) (=);
run;
date=1116 year=2014 month=11 day=16 date2=16NOV2014 date2char=11/16/2014
date=1209 year=2014 month=12 day=9 date2=09DEC2014 date2char=12/09/2014
date=101 year=2015 month=1 day=1 date2=01JAN2015 date2char=01/01/2015
date=102 year=2015 month=1 day=2 date2=02JAN2015 date2char=01/02/2015
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 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: