07-07-2015 03:30 PM
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
07-07-2015 03:55 PM
Not sure what you want the second part to look like but this might get you there. This takes care of 1 and 2:
infile cards dsd;
length date $10.;
data want(rename=(date2 = date));
format date2 mmddyy10.;
date2 = input(put(tranwrd(date,"_","/"),$10.),mmddyy10.);
do i = 1 to 50;
count + 1;output;
drop date i;
07-07-2015 04:02 PM
The MMDDYY informat will handle this input without any of these modifications as any delimiter will work.
input dt mmddyy10.;
format dt date9.;
07-08-2015 04:00 AM
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.
07-08-2015 09:28 AM
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.
07-08-2015 04:41 AM
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.