BookmarkSubscribeRSS Feed
BETO
Fluorite | Level 6

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;

8 REPLIES 8
CTorres
Quartz | Level 8

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

CTorres
Quartz | Level 8

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

kaade
SAS Employee

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;

Reeza
Super User

How do you know what year goes with what record?

ballardw
Super User

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.

user24feb
Barite | Level 11

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;

BETO
Fluorite | Level 6

Yes with out year it's diffcult  to get it thanks for the assistance

Tom
Super User Tom
Super User

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

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!

What is Bayesian Analysis?

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.

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
  • 8 replies
  • 2229 views
  • 6 likes
  • 7 in conversation