Help using Base SAS procedures

Converting a Date Format

Reply
Contributor
Posts: 65

Converting a Date Format

Hi,

I am trying to convert following date into a simple format.

Wed May 18 16:58:32 2011

I am looking to have something like this: May 18 2011 or 05/18/2011

Can anyone please guide me getting this sort of format?
SAS Super FREQ
Posts: 8,864

Re: Converting a Date Format

Hi:
More information is needed. The date probably doesn't exist in a vacuum. Is this date a variable in a SAS table or dataset? Is this date a variable or column in a database table? Is the variable a character variable (one long text string) or is the variable a numeric variable that represents an offset from a particular date (as it would be if it were a SAS date variable)?? Could the data be in a "raw" text file that you need to read into SAS format? Are you using SQL to retrieve this column from a database table???

Do you have multiple rows or observations and if the variable is character, do ALL the rows follow the same general format:
[pre]
DAY-OF-WEEK MONTH DAY HH:MMSmiley FrustratedS YEAR
This is what you showed:
Wed May 18 16:58:32 2011

But which of these would be found in the data? Is there a pattern to the current format?
Thursday June 16 15:55:15 2011
Thu Jun 16 15:55:15 2011
[/pre]

with a space between each discrete piece of information??? Is DAY-OF-WEEK always 3 characters? Is Thursday spelled out or is it represented as Thu? Is the month spelled out?? Would it be September or Sep???? June or Jun???

The most immediately relevant piece of information is finding out whether this is a long character string or whether it is some kind of numeric variable. Are you trying to read in the data or are you trying to alter the display of the data for a report or are you trying to alter the form of the data as it is stored in your SAS table??

cynthia
Contributor
Posts: 65

Re: Converting a Date Format

Posted in reply to Cynthia_sas
Cynthia,

Here's some additional information:

This date is a part of my dataset and basically represent the modificationdate column. Regarding the datatype it is a "Character" variable. The dataset is residing in a SAS table. The reason why I want to convert the date to a date format is becuase, I want to compare the date with a specific date let's say today's date 06/21/2011.

All the date values in the table have the same format. Here's few more example:

Fri Feb 25 12:51:57 2011
Fri Mar 11 13:12:56 2011
Mon Mar 21 09:48:58 2011
Fri Apr 1 11:49:19 2011
Fri Apr 8 14:24:26 2011
Mon Apr 18 17:37:01 2011
Fri Apr 22 10:01:57 2011
Fri Apr 29 11:22:39 2011
Thu May 26 12:34:03 2011
Fri May 27 15:16:17 2011

The day of the week is a three (3) character long
The month is represented in three (3) character only .. Sep .. Oct .. Nov

Hope this helps!
SAS Super FREQ
Posts: 8,864

Re: Converting a Date Format

Hi:
If you look at your character string, with an eye toward breaking it up into "chunks" or "pieces", every piece is separated from another piece by a space. This is a perfect situation to use the SCAN function. Following the pattern you laid out, then, you can create 5 character variables from your long string by using the SCAN function (let's assume you call the long character string variable CHARDATE)
[pre]
dow = scan(chardate,1,' ');
mon = scan(chardate,2,' ');
day = scan(chardate,3,' ');
time = scan(chardate,4,' ');
year = scan(chardate,5,' ');
[/pre]

Then once you have broken up your long character string into pieces, you can use the concatenation operators or the CAT functions to create new character strings, such as: 25Feb2011 or 11Mar2011 -- dealing with DATE values only.

With a new character variable (let's call this new variable CHARDATE2), you're still not ready to subtract 2 dates yet. You have to turn the newly arrranged character date (without the time or day of week) into a numeric date value, something like this:
[pre]
chardate2 = catt(day,mon,year);
numeric_date = input(chardate2,date9.);
[/pre]

Now, the NUMERIC_DATE variable will represent the number of days since Jan 1, 1960 - -so you could subtract the numeric_date from a date of your choosing (either another numeric date value or a date constant, as I show), to come up with a difference in number of days:
[pre]
diff = '21Jun2011'd - numeric_date;
[/pre]

The code could be streamlined quite a bit. I split the variables into so many pieces to illustrate how the SCAN function works and because you may want to create a date/time variable by creating a character string that can be transformed with the INPUT function using a date/time informat.

Hopefully, this will get you started.

cynthia
Respected Advisor
Posts: 4,173

Re: Converting a Date Format

Hi

Haven't found a SAS informat which directly converts your datetime string but by re-shufflling the string a bit you could bring it into an order where you can use a datetime informat.


data want;
infile datalines truncover;
input DTTMstring $24.;

retain pidDTTM pidDT;
if _n_=1 then
do;
pidDTTM =prxparse('s/(\w+) (\w+) (\d+) (\d+:\d+:\d+) (\d+)/$3$2$5 $4/oi');
pidDT =prxparse('s/(\w+) (\w+) (\d+) (\d+:\d+:\d+) (\d+)/$3$2$5/oi');
drop pidDTTM pidDT;
end;

format varDTTM datetime21.;
format varDT date9.;
varDTTM =input(prxchange(pidDTTM,1,DTTMstring),datetime.);
varDT =input(prxchange(pidDT,1,DTTMstring),date9.);

datalines;
Fri Feb 25 12:51:57 2011
Fri Mar 11 13:12:56 2011
Mon Mar 21 09:48:58 2011
Fri Apr 1 11:49:19 2011
Fri Apr 8 14:24:26 2011
Mon Apr 18 17:37:01 2011
Fri Apr 22 10:01:57 2011
Fri Apr 29 11:22:39 2011
Thu May 26 12:34:03 2011
Fri May 27 15:16:17 2011
;
run;

proc print data=want;
run;


HTH
Patrick Message was edited by: Patrick
Ask a Question
Discussion stats
  • 4 replies
  • 700 views
  • 0 likes
  • 3 in conversation