How to convert 02_01_2015 to SAS date format

[mrajendranvasanthi]

i have a set of 50 excel files and the name of each file is the date. I used a macro to read the files content and read the filename to create a variable filedate in the dataset. the variable filedate has character data like 01_01_2015 , 01_02_2015.

1) i need to convert this to sas date format MMDDYY10.

2) i need to create another variable which contains value from 1-50 for each filedate

can someone help me out in this?da

[Steelers_In_DC]

Not sure what you want the second part to look like but this might get you there.  This takes care of 1 and 2:

data have;

infile cards dsd;

length date $10.;

input date$;

cards;

01_01_2015

;

run;

data want(rename=(date2 = date));

format date2 mmddyy10.;

set have;

date2 = input(put(tranwrd(date,"_","/"),$10.),mmddyy10.);

do i = 1 to 50;

count + 1;output;

end;

drop date i;

run;

[Reeza]

1. input(date_text, mmddyy10.);

2. Use By processing? Hard to say without more information.

Are these all text files you imported?

[FriedEgg]

The MMDDYY informat will handle this input without any of these modifications as any delimiter will work.

data;

input dt mmddyy10.;

format dt date9.;

cards;

01_01_2015

02_01_2015

;

run;

01JAN2015

01FEB2015

[viveklanka]

for the first part of the question use the direct input function

date2= input(date, anydtdte12.); OR date2= input(date, mmddyy10.)

format date2 mmddyy10.;

This will convert the char variable to numeric with the date format.

And please clarify the second part of the question.

[FriedEgg]

If you are going to use the anydtdte function, you should set the option datestyle to the appropriate value (mdy, in this case) or, you can end up with the wrong dates.  It is also why you should use the correct informat, when one exists.  You should use the mmddyy informat with this example data.

[RW9]

My question would be why?  Firstly Excel isn't a good data transfer format, secondly naming a file with date is just asking for trouble, what happens when you get two files on the same day?  What kind of transfer agreement do you have in place?   If this is coming from a database, ask them to send a cumulative file, CSV based, i.e. one complete file, it should be pretty easy.  This is something that seems to come up as a question more and more on here at the moment, "i have been given xyz Excel files and need to process them", and my response is always, don't, get a proper data transfer document in place detailing what and when data will be transferred.  Use a data transfer format - e.g. structured like CSV, XML, database dump.  It will make your life easier in the long run.