BookmarkSubscribeRSS Feed
mrajendranvasanthi
Fluorite | Level 6

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

6 REPLIES 6
Steelers_In_DC
Barite | Level 11

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
Super User

1. input(date_text, mmddyy10.);

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

Are these all text files you imported?

FriedEgg
SAS Employee

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
Fluorite | Level 6


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
SAS Employee

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
Diamond | Level 26 RW9
Diamond | Level 26

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.

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
  • 6 replies
  • 1145 views
  • 1 like
  • 6 in conversation