I have a date variable which is character with length as 50. and i want to convert it to date9.
sample of date:
AESTDTC
2017-03-14T08:00
2016-12-16
2017-04-10T20:00
i am using the below code:
data nkd;
set adae;
format AESTDT date9.;
AESTDT = input( AESTDTC, MMDDYY10.);
run;
i get error as below. can anyone help me out in this
NOTE: Invalid argument to function INPUT at line 51 column 241.
Please post test data in the form of a datastep, I think this has been mentioned several times now.
First point with any data is to get it into a useable format. What you have there are ISO dates, pretty standard in the SDTM you are displaying there. Now the first question would be what are you doing to want this data, genrally speaking you would goto ADaM first where you can have both the text for display and numerics for calculation.
You can however process the data, first, I would have something in the documentation which handles missing or partial dates/times - this is far more important than any coding requirements. Then its simply a matter of implementing these rules. For instance in the below I have assumed 00:00 if time is missing, and all will have 00 for miliseconds as this is not present in the data. Once I apply this rule then handle dates/times formatting becomes the simplest of tasks:
data have; length aestdtc $16; input aestdtc $; aestdtc=cats(aestdtc,"T00:00"); aestdtc_n=input(cats(aestdtc,":00"),e8601dt.); aestdtc_mmddyy=datepart(aestdtc_n); format aestdtc_n datetime. aestdtc_mmddyy mmddyy10.; datalines; 2017-03-14T08:00 2016-12-16 2017-04-10T20:00 ; run;
Do this:
data nkd;
set adae;
format AESTDT date9.;
AESTDT = input(substr(AESTDTC,1,10),yymmdd10.);
run;
Although I'd rather expand incomplete timestamps with a default time value and use the e8601dt informat, so that the timestamps are preserved.
like this?
data have;
input AESTDTC $50.;
datalines;
2017-03-14T08:00
2016-12-16
2017-04-10T20:00
;
data want;
set have;
date = input(substr(AESTDTC,1,10), yymmdd10.);
format date date9.;
run;
Thanks,
Sorry for this again. I now can see i have data which has missing or partial and it causes error "2016-06" missing day.
In proc report i am mentioning format=datetime7.
and i get all date as 01jan60(for all obs)
desired format is "06SEP13"
Not sure I understand this, so you have some datapoints where the day of the date is missing?
sorry for not being clear.
Now i made the dates in numeric format date9. as example 02JAN2017
and in proc report for the variable i am using format=date7.; as i need the final date in "06SEP13" but its not working
A couple of items are concerning.
First, if you have a variable that takes on date values, why would you try to apply a DATETIME format? That's what gives you 01JAN60. You can use the DATE9 format or the DATE7 format, but not a DATETIME format. Your variable simply isn't storing any time-related information and a pure date should use a date format.
Second, why change the format at all? You already assigned a DATE9 format to your variable. What advantage could you possibly get in your report by shifting to two-digit years?
Finally, it doesn't look like you are telling the full story. What code did you run, so that a missing month should default to SEP?
Please post test data in the form of a datastep, I think this has been mentioned several times now.
First point with any data is to get it into a useable format. What you have there are ISO dates, pretty standard in the SDTM you are displaying there. Now the first question would be what are you doing to want this data, genrally speaking you would goto ADaM first where you can have both the text for display and numerics for calculation.
You can however process the data, first, I would have something in the documentation which handles missing or partial dates/times - this is far more important than any coding requirements. Then its simply a matter of implementing these rules. For instance in the below I have assumed 00:00 if time is missing, and all will have 00 for miliseconds as this is not present in the data. Once I apply this rule then handle dates/times formatting becomes the simplest of tasks:
data have; length aestdtc $16; input aestdtc $; aestdtc=cats(aestdtc,"T00:00"); aestdtc_n=input(cats(aestdtc,":00"),e8601dt.); aestdtc_mmddyy=datepart(aestdtc_n); format aestdtc_n datetime. aestdtc_mmddyy mmddyy10.; datalines; 2017-03-14T08:00 2016-12-16 2017-04-10T20:00 ; run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.