topic Re: Changing text into date (with special attention to format) in SAS Data Management

Changing text into date (with special attention to format)

ilikesas — Wed, 03 Dec 2014 03:54:31 GMT

Hi,

I have the following column in a file

date
Current
12/12/2010
Current
3/12/2009

What I would like to have is for "Current" to be transformed into the date 1/12/2013, and for the other dates to, remain the way they are.

This might not be a difficult task but I have a problem with the format because the in Excel file from which I infile my data to SAS this very column is of mixed format and I have problem in SAS because of this.

Thank you!

Re: Changing text into date (with special attention to format)

art297 — Wed, 03 Dec 2014 04:20:16 GMT

Not sure if I correctly understand what you have and want, but it sounds something like the following:

data have;

informat date $11.;

input date &;

cards;

Current

12/12/2010

Current

3/12/2009

Not Current

3/12/2009

;

data want (keep=date);

set have (rename=(date=_date));

format date ddmmyy10.;

retain current;

if anyalpha(_date) then do;

if _date eq 'Current' then current=1;

else current=0;

delete;

end;

else do;

if current then date='1dec2013'd;

else date=input(_date,ddmmyy10.);

end;

run;

Re: Changing text into date (with special attention to format)

RW9 — Wed, 03 Dec 2014 09:25:26 GMT

Assuming your data is character (which it would need to be to contain Current:

data want;

set have;

date=tranwrd(date,"Current",put(today90,ddmmyy10.));

run;

Re: Changing text into date (with special attention to format)

Loko — Wed, 03 Dec 2014 12:08:32 GMT

Hello,

With help of proc format:

data frm;
FMTNAME='$cnvfmt';
START='Current';
LABEL=put(today(),ddmmyy10.);
type='J';
output;
FMTNAME='$cnvfmt';
START='other';
LABEL='_same_';
type='J';
output;
run;

proc format cntlin=frm;
run;

data want;
set have;
a=input(a,$cnvfmt15.);
run;

Re: Changing text into date (with special attention to format)

art297 — Wed, 03 Dec 2014 14:48:57 GMT

After seeing the other interpretation of your question, here is another alternative:

data have;

informat date $11.;

input date &;

cards;

Current

12/12/2010

Current

3/12/2009

Current

3/12/2009

;

data want (keep=date);

set have (rename=(date=_date));

format date ddmmyy10.;

if _date eq 'Current' then date='1dec2013'd;

else date=input(_date,ddmmyy10.);

run;

Re: Changing text into date (with special attention to format)

ilikesas — Thu, 04 Dec 2014 03:48:39 GMT

Hi Arthur,

I created the following Excel file:

date

Current

12/12/2010

Current

03/12/2009

Current

But when I imported it to Excel it gave me the following:

1	Current
2	40524
3	Current
4	40150
5	Current

so the "Current" remained the same, but the dates are now numbers, and when I ran the second code it gave me the following:

1	01/12/2013
2	04/05/1924
3	01/12/2013
4	04/01/1950
5	01/12/2013

so 12/12/2010 was transformed into 4/5/1924 !!! I guess that Excel and SAS have different date starting points, so how to correct for this?

Thank you

Re: Changing text into date (with special attention to format)

ilikesas — Thu, 04 Dec 2014 03:59:51 GMT

Hi Loko,

did your code for my infiled excel and got the following:

1	Current	.
2	40524	.
3	Current	.
4	40150	.
5	Current	.

Re: Changing text into date (with special attention to format)

ilikesas — Thu, 04 Dec 2014 04:02:12 GMT

Hi RW9,

did your code and got the following:

1	01/12/2013
2	01/12/2013

it changed the Curtent into the desired date but omitted the other "good" dates.

Maybe the problem is with date and I should use a mixed string/number format instead?

Re: Changing text into date (with special attention to format)

art297 — Thu, 04 Dec 2014 04:48:01 GMT

You're not telling us what you did and you never mentioned anything about what you did in SAS or how you moved data between SAS and Excel.

The numbers you showed are unformatted Excel dates. If you subtract 21916 from each, and then (in Excel) format them as dates, you'll see that they represent 12/12/2010 and 3/12/2009, respectively.

Re: Changing text into date (with special attention to format)

ilikesas — Thu, 04 Dec 2014 05:25:51 GMT

Hi Arthur and thanks a lot for the late night reply!!!

I tried to go around the formatting by doing the following:

PROC IMPORT OUT= current_change

DATAFILE= "C:\Documents and Settings\HP_Administrator\Desktop\current.xls"

DBMS=EXCEL REPLACE;

sheet = "Sheet1";

GETNAMES=YES;

MIXED=NO;

SCANTEXT=YES;

USEDATE=YES;

SCANTIME=YES;

RUN;

at first the SAS looked just like my Excel, then I did your code with a slight modification:

data current_change2 (drop=_date) ;

set current_change (rename=(date=_date));

if _date eq 'Current' then date='01/12/2013';

else date=_date;

run;

and this gave me what appeared to be all dates, but when I tried to merge by DATE my table with the new "current-transformed" dates column with a table that contains a column of "pure" dates, that is, only dates and no numbers or words, I get an error message:

ERROR: Expression using equals (=) has components that are of different data types.

I guess that my new dates aren't in date format, so is there a way to make it a date format?

Thank you!

Re: Changing text into date (with special attention to format)

ilikesas — Thu, 04 Dec 2014 06:05:10 GMT

Hi again, I just hope that I am not bothering you too much

I think that I actually found the way how to change the format, its actually a question by another user on the blog

data current_change3 (drop=date);

set current_change2;

format date2 ddmmyy10.;

date2=input(trim(date),ddmmYY10.);

run;

thanks a lot for everyone!

Re: Changing text into date (with special attention to format)

RW9 — Thu, 04 Dec 2014 09:03:03 GMT

Sorry, I had assumed you had a dataset, there was a typo in my program, see below for proper code.

If you are doing an import then I would highly recommend that you do not use proc import. That is where you problem lies. Excel is not a tool for the transfer of data. It is an unstructured document which will quite happily allow you have test in one cell and dates in another. SAS (like most other data orientated software) operates on a table basis, i.e. all the data in one column fits to one rule. When you proc import SAS trying to guess what you want to do with import data. When it scans the column it gets back some text, and some Excel format dates. If you don't tell it anything else then it will import the lot as text as then all the data can come in. With the options you have put in your import then it will only take date values.

My suggestion would be to

a) use a proper data capture tool which will provide you with structured data which can be logically cleaned etc.

b) write your import programs yourself - you should know what data you want in, how it looks etc. don't let software "guess" for you.

Updated Code:

data a;

date="Current"; output;

date="12/12/2010"; output;

date="Current"; output;

date="3/12/2009"; output;

run;

data b;

set a;

new_date=input(tranwrd(date,"Current",put(today(),ddmmyy10.)),ddmmyy10.);

run;

Re: Changing text into date (with special attention to format)

art297 — Thu, 04 Dec 2014 14:16:13 GMT

Dates in both SAS and Excel are numbers. In SAS the value is equal to the number of days since Jan 1st, 1960.

In Excel the value is equal to the number of days since Dec 31st, 1899, which is why I suggested subtracting 21,916 from each date.

The format is simply how the dates are displayed.

Finally, '1dec2013'd is known as a date constant in SAS, thus date= '1dec2013'd produces a number equal to the number of days since Jan 1st, 1960.