DATA Step, Macro, Functions and more

Date manupulations

Reply
Frequent Contributor
Posts: 76

Date manupulations

Hi,

 

I have dates like 2017-01--     

I need my output as 2017-01

 

I need a code without hard coding

 

 

 

 

Thanks.

PROC Star
Posts: 1,215

Re: Date manupulations

So your dates are stored as text or?

Frequent Contributor
Posts: 76

Re: Date manupulations

stored as character
PROC Star
Posts: 1,215

Re: Date manupulations

Something like this?

 

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

data want;
	set have;
	NumDate=input(compress(CharDate, "-"), yymmn6.);
	format NumDate yymmn6.;
run;
Frequent Contributor
Posts: 76

Re: Date manupulations

The output should be 2017-01
PROC Star
Posts: 1,215

Re: Date manupulations

Then use YYMMd format

 

 

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

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

 

Frequent Contributor
Posts: 76

Re: Date manupulations

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?

Super User
Super User
Posts: 9,421

Re: Date manupulations

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-dat...

 

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.

 

Super User
Posts: 13,321

Re: Date manupulations


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.

 

Super User
Super User
Posts: 9,421

Re: Date manupulations

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.

Ask a Question
Discussion stats
  • 9 replies
  • 115 views
  • 0 likes
  • 4 in conversation