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!
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;
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;
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?
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;
Hi Loko,
did your code for my infiled excel and got the following:
1 | Current | . |
---|---|---|
2 | 40524 | . |
3 | Current | . |
4 | 40150 | . |
5 | Current | . |
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;
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
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.
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!
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!
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;
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.