topic Re: date as numeric in data step in SAS Programming

date as numeric in data step

GN0001 — Fri, 25 Mar 2022 22:13:59 GMT

Hello team,

I have a date variable of numeric data type. I put this variable in a year function to get the year portion of the date.

The result is 2022 as a character. Then I use this 2022 in a single quote in a where clause in a proc sql that groups the data, as soon as I put a.year = '2022', it returns 0 rows back.

proc sql;
create table mytable as
select a,*, b.id, b.event,  b.year, b.codetype, count(a.event) 
from thattable a inner join thistable b on a.id=b.id
where a.year ='2020' and b.codeType = 'hhhh'
group by a.id, b.id, b.codetype,b.year;
quit;

Regards,

blue & blue

Re: date as numeric in data step

Astounding — Fri, 25 Mar 2022 22:55:37 GMT

The output of the YEAR function is numeric, not character. Remove the quotes.

Re: date as numeric in data step

SASKiwi — Fri, 25 Mar 2022 23:28:51 GMT

You can easily fix this type of problem for yourself by reviewing your SAS log. Most likely there will be notes, warnings or errors giving clues as to what has happened.

Did you do this?! If not it is time to learn about using your SAS log to your advantage.

Also you can easily check the variable definitions in MYTABLE by using PROC CONTENTS. That will likely confirm that YEAR is numeric.

Re: date as numeric in data step

GN0001 — Sat, 26 Mar 2022 02:49:11 GMT

Hello.

the date is numeric in the dataset from which I read my data. It is displayed as 2022-01-12 in the dataset.

logs says: Error: where clause operator requires compatible variable.

note: Numeric values have been converted to character values at th places bive by: (line): (column).

Thanks,

b&b

Re: date as numeric in data step

Tom — Sat, 26 Mar 2022 04:52:06 GMT

If you assign a numeric value into a character variable SAS will convert the number into a string that is RIGHT ALIGNED in the character variable.

Try

where left(a.year) ='2020'

That will remove the leading spaces from the character variable.

Re: date as numeric in data step

SASKiwi — Sat, 26 Mar 2022 05:04:30 GMT

Did you try removing the quotes as suggested by @Astounding ? That is the most likely cause of that error.

Re: date as numeric in data step

Kurt_Bremser — Sat, 26 Mar 2022 05:16:06 GMT

Show the complete log of the step where you create year using the YEAR function.

Re: date as numeric in data step

GN0001 — Mon, 28 Mar 2022 21:40:52 GMT

Hello team,

I resolved this (that is what I think)

When I coded as this:

data mydate (Keep= id, dateofservice, year, month)

The year is defined as string not numeric.

so then year = Year(dateofservice) is converted to a character, which is not correct.

What I did, I convert year into numeric in data steps before year function, so the result of year function in the year variable would be a numeric not a character data type.

So, when I filter data in where clause, my data is filtered correct.

Respectfully,

b&b

Re: date as numeric in data step

GN0001 — Mon, 28 Mar 2022 21:42:55 GMT

Hello team,

I can present some parts of the log; I can't display all the log. It belongs to my company where the information should be kept confident.

Thanks,

b&b

Re: date as numeric in data step

SASKiwi — Mon, 28 Mar 2022 22:20:41 GMT

Disguise sensitive names using paint tools before posting. SAS errors, warnings and notes aren't confidential and we need to see them to be able to help.

Alternatively make up an example to demonstrate your problem and post that.

Re: date as numeric in data step

Kurt_Bremser — Tue, 29 Mar 2022 09:26:41 GMT

Post the complete log as text, and overwrite sensitive names with something easily recognized, e.g. <redacted>, or a string of capital X's.

The log (and always, the complete log) is our #1diagnostic tool (see Maxim 2).

Re: date as numeric in data step

GN0001 — Tue, 29 Mar 2022 22:46:09 GMT

Hello SASKiwi,
I can't email anything out from my work email. If I want to reconstruct them that might take a long time. I hope this makes sense for everyone. The log is in my work computer.
Thanks,
b&b

Re: date as numeric in data step

GN0001 — Tue, 29 Mar 2022 22:47:38 GMT

Hello teamate,

I can't email the log from my work email, unfortunately.

Thanks,

b&b

Re: date as numeric in data step

SASKiwi — Wed, 30 Mar 2022 01:14:34 GMT

@GN0001 - Do you not have internet access on your work computer? If you do have internet access you can just copy from your SAS log and paste into SAS Communities post.

Re: date as numeric in data step

Kurt_Bremser — Wed, 30 Mar 2022 09:51:43 GMT

@GN0001 wrote:

Hello teamate,

I can't email the log from my work email, unfortunately.

Thanks,

b&b

No need for mails. Just copy/paste.

Re: date as numeric in data step

GN0001 — Mon, 11 Apr 2022 02:33:22 GMT

Hello saskiwi,

This is not doable. That has legal consequences.

Regards,

blue & blue