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
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.
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
Did you try removing the quotes as suggested by @Astounding ? That is the most likely cause of that error.
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
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.
Show the complete log of the step where you create year using the YEAR function.
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
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 - 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.
Hello saskiwi,
This is not doable. That has legal consequences.
Regards,
blue & blue
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).
Hello teamate,
I can't email the log from my work email, unfortunately.
Thanks,
b&b
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.