BookmarkSubscribeRSS Feed
molla
Fluorite | Level 6

Hi,

 

I have dates like 2017-01--     

I need my output as 2017-01

 

I need a code without hard coding

 

 

 

 

Thanks.

9 REPLIES 9
PeterClemmensen
Tourmaline | Level 20

So your dates are stored as text or?

molla
Fluorite | Level 6
stored as character
PeterClemmensen
Tourmaline | Level 20

Something like this?

 

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

data want;
	set have;
	NumDate=input(compress(CharDate, "-"), yymmn6.);
	format NumDate yymmn6.;
run;
molla
Fluorite | Level 6
The output should be 2017-01
PeterClemmensen
Tourmaline | Level 20

Then use YYMMd format

 

 

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

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

 

molla
Fluorite | Level 6

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

 

ballardw
Super User

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

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 992 views
  • 0 likes
  • 4 in conversation