date as numeric in data step

[GN0001]

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

[Astounding]

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

[SASKiwi]

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.

[GN0001]

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

[SASKiwi]

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

[GN0001]

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

[Tom]

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.

[Kurt_Bremser]

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

[GN0001]

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

[SASKiwi]

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. 

[GN0001]

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

[SASKiwi]

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

[GN0001]

Hello saskiwi,

This is not doable. That has legal consequences.

Regards,

blue & blue

[Kurt_Bremser]

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

[GN0001]

Hello teamate,

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

Thanks,

b&b