BookmarkSubscribeRSS Feed
ilikesas
Barite | Level 11

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!

12 REPLIES 12
art297
Opal | Level 21

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

ilikesas
Barite | Level 11

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?

Loko
Barite | Level 11

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;

ilikesas
Barite | Level 11

Hi Loko,

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

1Current .
240524 .
3Current .
440150 .
5Current .
art297
Opal | Level 21

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;


ilikesas
Barite | Level 11

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

art297
Opal | Level 21

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.

ilikesas
Barite | Level 11

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!

ilikesas
Barite | Level 11

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!

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

art297
Opal | Level 21

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.

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 12 replies
  • 1756 views
  • 3 likes
  • 4 in conversation