SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Extract YYYYDD from DATETIME26.7 keep result as numberic

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Extract YYYYDD from DATETIME26.7 keep result as numberic

[ Edited ]

Hi, I have a DATETIME26.7 string and I need to get it into the format of YYYYDD (YearDay), obviously this is a pretty useless date function so I don't think anything like this exists out the box. It's not my decision to use this value and I don't know the reasoning why so please don't let it become a debate over that...

 

I'm doing this as an expression in DI Studio if that makes a difference.

 

My first idea was a substr to extract the year and the day and then catx them together but that doesn't work on numeric fields, I've tried substrn but I just get '0' no matter what I try, and catx still wouldn't work on numeric values anyway (although it claims it does).

 

So for an example:

Input: 19JUL2016:12:00:00.0000000

Output: 201619

 

This is going to also be part of a case when and I need to end up with it as a numeric value still. I think it might need the format changing to character and back again, but can this be done as part of my case when rather than through multiple extracts before/after.

 

This is essentailly where I'm up to (which errors)

 

CASE WHEN DATE_OF_EXIT ^= .  &  EXIT_NOTIFICATION_STATUS = "1"
THEN CATX("",substrn(DATE_OF_EXIT,1,4),substrn(DATE_OF_EXIT,6,2))
ELSE . END

 

This gives me:

"ERROR: Result of WHEN clause 2 is not the same data type as the preceding results.
ERROR: Character expression requires a character format."

 

Any help appreciated, thanks.


Accepted Solutions
Solution
3 weeks ago
Super User
Posts: 5,071

Re: Extract YYYYDD from DATETIME26.7 keep result as numberic

I can't test this right now, but it looks like you're just taking the wrong pieces.  If DATE_OF_EXIT is character and the new variable can be character:

 

CASE WHEN DATE_OF_EXIT ^= .  &  EXIT_NOTIFICATION_STATUS = "1"
THEN CAT(substr(DATE_OF_EXIT,6,4), substr(DATE_OF_EXIT,1,2))
ELSE . END

 

Notice switching to SUBSTR for a character field.  For other possibilities (incoming string is numeric, or output should be numeric), other functions can handle that:

 

input(results of CAT, 6.) to get a numeric output

 

For a numeric input and numeric output:

 

cats(year(datepart(DATE_OF_EXIT)), day(datepart(DATE_OF_EXIT)))

 

Apply INPUT to the result of CATS to get the output variable as numeric.

View solution in original post


All Replies
Super User
Super User
Posts: 7,392

Re: Extract YYYYDD from DATETIME26.7 keep result as numberic

I think your over complicating it, simply:

date=input(scan(in_date,1,":");
format date yymmn6.;

If its a string (not seen a datetime that long, so not sure if its valid, if it is a numeric then:

date=datepart(in_date);
format date yymmn6.;
Occasional Contributor
Posts: 12

Re: Extract YYYYDD from DATETIME26.7 keep result as numberic

Thanks, that will work for the YYYYMM format I also need (but was reasonably confident about, I'll give your methods ago now.

 

My main issue is that I need YYYYDD, as in YearDay, which doesn't seem straight forward.

Super User
Super User
Posts: 7,392
Occasional Contributor
Posts: 12

Re: Extract YYYYDD from DATETIME26.7 keep result as numberic

Thanks, I hadn't seen that, unfortunately though it looks as if it does the day as a 1-365, I need the day as it appears in the month.

 

So 26th July 2017 would give me 201726, rather than 2016207 which I think Julian would do.

 

It seems like a useless data value to me without the month included, but you gotta do what you gotta do.

Super User
Super User
Posts: 7,392

Re: Extract YYYYDD from DATETIME26.7 keep result as numberic

Then you would need to do that by hand, there is no format to cover such a thing (as it makes no sense - I can understand if you wanted year and day of study for instance, but not day of an non-specific human term such as month without descriptor).  E.g

num=input(cats(put(year(datepart(date)),z4.),put(day(datepart(date)),z2.)),best.);
Solution
3 weeks ago
Super User
Posts: 5,071

Re: Extract YYYYDD from DATETIME26.7 keep result as numberic

I can't test this right now, but it looks like you're just taking the wrong pieces.  If DATE_OF_EXIT is character and the new variable can be character:

 

CASE WHEN DATE_OF_EXIT ^= .  &  EXIT_NOTIFICATION_STATUS = "1"
THEN CAT(substr(DATE_OF_EXIT,6,4), substr(DATE_OF_EXIT,1,2))
ELSE . END

 

Notice switching to SUBSTR for a character field.  For other possibilities (incoming string is numeric, or output should be numeric), other functions can handle that:

 

input(results of CAT, 6.) to get a numeric output

 

For a numeric input and numeric output:

 

cats(year(datepart(DATE_OF_EXIT)), day(datepart(DATE_OF_EXIT)))

 

Apply INPUT to the result of CATS to get the output variable as numeric.

Occasional Contributor
Posts: 12

Re: Extract YYYYDD from DATETIME26.7 keep result as numberic

[ Edited ]

Great stuff thanks @Astounding, it's now working, it's a numeric field so I went with the second one:

 

@RW9 thanks that works fine as well, I went with Astoundings option just because I understand the syntax better, rather than the best32. format and the zX that I'm not too sure about.

 

CASE WHEN DATE_OF_EXIT ^= .  &  EXIT_NOTIFICATION_STATUS = "1"
THEN input(cats(year(datepart(DATE_OF_EXIT)),day(datepart(DATE_OF_EXIT))),6.)
ELSE . END

 

 

Thank you both for the assistance.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 369 views
  • 2 likes
  • 3 in conversation