topic Re: Query builder - Add date value (year) instead of numeric value in SAS Enterprise Guide

Query builder - Add date value (year) instead of numeric value

user34567 — Fri, 15 Mar 2024 10:18:51 GMT

Hi,

In the query builder I added a computed column to add a year (2024) to the data set. But when I run the changes I get the output 1965 instead of 2024.

Computed item: PUT(YEAR(2024), 4.)

I really need it to be in date format and not a numeric format that's why I used the calculated item above.

What do I have to do differently?

Many thanks in advance!

Re: Query builder - Add date value (year) instead of numeric value

PaigeMiller — Fri, 15 Mar 2024 10:40:08 GMT

If you have a variable whose value is the number 2024, you don't need to format it and you don't need to use it inside PUT(). You can simply use the variable which has the value of 2024.

Please explain further, show us the inputs and the desired output. What does it mean to "add a year to a data set"? SHOW US.

Re: Query builder - Add date value (year) instead of numeric value

LinusH — Fri, 15 Mar 2024 11:24:05 GMT

If you need year as textual just add it as a constant in your expression:
"2024"

Re: Query builder - Add date value (year) instead of numeric value

user34567 — Fri, 15 Mar 2024 12:21:54 GMT

Hi @PaigeMiller, thank you for responding. If I just add "2024" it wel be marked as a numeric value instead of date. In SAS Viya I would like to link the years from two different datasets to eachother but I cannot do that when it is not seen as a real date.

Re: Query builder - Add date value (year) instead of numeric value

LinusH — Fri, 15 Mar 2024 12:36:15 GMT

I'm confused, if I add "2024" in the calculated columns expression, a character variable is created.

Re: Query builder - Add date value (year) instead of numeric value

user34567 — Fri, 15 Mar 2024 12:53:29 GMT

Exactly, but I need it to be a date format YYYY, not numeric

Re: Query builder - Add date value (year) instead of numeric value

Tom — Fri, 15 Mar 2024 13:11:00 GMT

@user34567 wrote:

Hi @PaigeMiller, thank you for responding. If I just add "2024" it wel be marked as a numeric value instead of date. In SAS Viya I would like to link the years from two different datasets to eachother but I cannot do that when it is not seen as a real date.

If you want to create a DATE value you need to specify the month and the day also. Normally people just use January 1st.

Re: Query builder - Add date value (year) instead of numeric value

PaigeMiller — Fri, 15 Mar 2024 13:20:17 GMT

Stop referring to formats here. SHOW US what you have at the start and then SHOW US the desired end result.

Re: Query builder - Add date value (year) instead of numeric value

LinusH — Fri, 15 Mar 2024 14:01:45 GMT

????

Re: Query builder - Add date value (year) instead of numeric value

Kurt_Bremser — Fri, 15 Mar 2024 14:27:53 GMT

@user34567 wrote:

Exactly, but I need it to be a date format YYYY, not numeric

A date in SAS is always numeric (count of days starting at 1960-10-01), which is displayed in human-readable form through a format of the date group.

The YEAR. format will display only the year part of such a value.

So, what do you have in both datasets (type and format of the existing variables), and along which value extracted from these do you want to join?

Re: Query builder - Add date value (year) instead of numeric value

Reeza — Fri, 15 Mar 2024 14:47:50 GMT

@user34567 wrote:

Hi,

In the query builder I added a computed column to add a year (2024) to the data set. But when I run the changes I get the output 1965 instead of 2024.

Computed item: PUT(YEAR(2024), 4.)

I really need it to be in date format and not a numeric format that's why I used the calculated item above.

What do I have to do differently?

Many thanks in advance!

Do not use a calculated item.

Change the format on the variable to be ~~YY4.~~ YEAR4. instead.

https://documentation.sas.com/doc/da/vdmmlcdc/8.1/leforinforref/p0jwqba6i1r0j1n1xe9ovnn0pamr.htm

Re: Query builder - Add date value (year) instead of numeric value

Tom — Fri, 15 Mar 2024 14:55:36 GMT

@user34567 wrote:

Exactly, but I need it to be a date format YYYY, not numeric

A DATE is numeric. But the number 2,024 is a day in July of 1965. So applying the YEAR() function or the YEAR format to the value 2,024 will yield 1965.

Assign and actual date to your new variable. Then attach a format, like YEAR4. that just displays the year part of the date value.

If VIYA supports SAS date constants use:

'01JAN2024'd

Re: Query builder - Add date value (year) instead of numeric value

user34567 — Mon, 18 Mar 2024 09:32:00 GMT

Thank you, this was very helpful! Now I also understand why my 'solution' was not working.