SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Changing text into date (with special attention to format)

Reply
Super Contributor
Posts: 413

Changing text into date (with special attention to format)

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!

PROC Star
Posts: 7,366

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

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;

Super User
Super User
Posts: 7,430

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

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;

Super Contributor
Posts: 413

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

Hi RW9,

did your code and got the following:

101/12/2013
201/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?

Super Contributor
Posts: 305

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

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;

Super Contributor
Posts: 413

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

Hi Loko,

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

1Current .
240524 .
3Current .
440150 .
5Current .
PROC Star
Posts: 7,366

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

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;


Super Contributor
Posts: 413

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

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:

1Current
240524
3Current
440150
5Current

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

101/12/2013
204/05/1924
301/12/2013
404/01/1950
501/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

PROC Star
Posts: 7,366

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

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.

Super Contributor
Posts: 413

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

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!

Super Contributor
Posts: 413

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

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!

Super User
Super User
Posts: 7,430

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

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;

PROC Star
Posts: 7,366

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

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.

Ask a Question
Discussion stats
  • 12 replies
  • 815 views
  • 3 likes
  • 4 in conversation