Hi,
I have dates like 2017-01--
I need my output as 2017-01
I need a code without hard coding
Thanks.
So your dates are stored as text or?
Something like this?
data have;
CharDate="2017-01--";
run;
data want;
set have;
NumDate=input(compress(CharDate, "-"), yymmn6.);
format NumDate yymmn6.;
run;
Then use YYMMd format
data have;
CharDate="2017-01--";
run;
data want;
set have;
NumDate=input(compress(CharDate, "-"), yymmn6.);
format NumDate YYMMd.;
run;
if the dates are 2017-01--01
2011-01--
I need output as 2017-01-01
2011-01
In this case we have to keep anycondition to check whether we have day in the given date or not or else we have any format to get the above mentioned output?
Please focus your question, by providing the required information so that we don't have to guess which is a waste of your time and ours. Start by:
Posting test data in the form of a datastep which shows the connotations in your data you want to change. If you are unsure on how to do this follow this post (note we just need a few rows which illustrate the issues):
Next up explain what the data processing rules are you want implement, you say for instance that 2017-01--01 should result in 2017-01-01, the question would be why, this is not correct format in any system ever: 2017-01--01, so why are you getting data in such a way - sounds like either something broke in a previous system or in previous code. Best to fix it there. What happens for month missing? Is it to be a SAS date variable, or should it be text output?
Finally showing some output as you would expect it is a good idea.
For the --, you could simply tranwrd this:
date=tranwrd(date,"--","-");
But I suspect you have more issues than that and fixing it in the first place would be my choice.
There are ISO date formats and informats you can work with - e8601dt. for instance, but they would be used for numeric dates, so partials would need imputations for missing components.
@molla wrote:
if the dates are 2017-01--01
2011-01--
I need output as 2017-01-01
2011-01In this case we have to keep anycondition to check whether we have day in the given date or not or else we have any format to get the above mentioned output?
?????
The above makes it look like you want to chang years???
Provide examples of all of the malformed date-appearing values and what date you want them actually treated as.
For example are you getting dates that look like
-01-30 (missing year)
2017-27 (missing month)
2017-- (missing month and day)
Provide the rules for handling each.
Note that the examples with input using the yymm formats are implying the day of the month will be 1. If you want a different day of the month then we need explicit rules as to what it should be.
Posting test data in the form of a datastep really illustrates problems. From what you post a simple:
data want; set have; date=substr(date,1,7); run;
Will work, but I doubt that is your full problem.
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.
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.