topic Re: Date manupulations in SAS Programming

Date manupulations

molla — Wed, 10 Jan 2018 11:04:46 GMT

Hi,

I have dates like 2017-01--

I need my output as 2017-01

I need a code without hard coding

Thanks.

Re: Date manupulations

PeterClemmensen — Wed, 10 Jan 2018 11:16:35 GMT

So your dates are stored as text or?

Re: Date manupulations

molla — Wed, 10 Jan 2018 11:23:38 GMT

stored as character

Re: Date manupulations

PeterClemmensen — Wed, 10 Jan 2018 11:27:43 GMT

Something like this?

data have;
	CharDate="2017-01--";
run;

data want;
	set have;
	NumDate=input(compress(CharDate, "-"), yymmn6.);
	format NumDate yymmn6.;
run;

Re: Date manupulations

RW9 — Wed, 10 Jan 2018 11:30:42 GMT

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.

Re: Date manupulations

molla — Wed, 10 Jan 2018 11:43:02 GMT

The output should be 2017-01

Re: Date manupulations

PeterClemmensen — Wed, 10 Jan 2018 12:07:47 GMT

Then use YYMMd format

data have;
	CharDate="2017-01--";
run;

data want;
	set have;
	NumDate=input(compress(CharDate, "-"), yymmn6.);
	format NumDate YYMMd.;
run;

Re: Date manupulations

molla — Wed, 10 Jan 2018 13:30:45 GMT

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?

Re: Date manupulations

RW9 — Wed, 10 Jan 2018 14:34:37 GMT

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):

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

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.

Re: Date manupulations

ballardw — Wed, 10 Jan 2018 15:58:44 GMT

@molla wrote:

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?

?????

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.