Extract YYYYDD from DATETIME26.7 keep result as numberic

[MRDM]

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.

[RW9]

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

[MRDM]

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.

[RW9]

Yearday format is called Julian format:

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000197940.htm

http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000197945.htm

[MRDM]

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.

[RW9]

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.);

[Astounding · Accepted solution]

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.

[MRDM]

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.