In operator does not work

[Sandeep77]

Hi all,

I am trying to find data which has month value as 202311, 202310, 202309, 202308 using the IN operator but when I use it, it does not produce any data whereas when I remove the where clause, it does shows me data which include 202311, 202310, 202309, 202308 in the month column. Here is the sample dataset:
Data Test;
infile cards expandtabs;
input ACCOUNT_ID FINREP_CURR_SEGMENT$ FINREP_CURR_SEGMENT_REASON$ DPD DRAWN_EXPOSURE MONTH;
datalines;
523668093 PERFORMING MEETREQUIREMENTS 0 21216.12 202309
592534742 PERFORMING MEETREQUIREMENTS 0 8281.46 202309
592596089 PERFORMING MEETREQUIREMENTS 0 11241.67 202309
523321583 PERFORMING MEETREQUIREMENTS 0 21188.56 202310
523355337 PERFORMING MEETREQUIREMENTS 0 14315.05 202310
;
run;
The month is in YYMMN6. format.

Proc sql;
create table Test as 
select a.ACCOUNT_ID,
a.FINREP_CURR_SEGMENT,
a.FINREP_CURR_SEGMENT_REASON,
a.DPD,
a.DRAWN_EXPOSURE,
b.Month
from Aug_Nov_check as a
inner join FSFINREP.FINREP_LM_AF_DET as b on a.ACCOUNT_ID = b.ACCOUNT_ID
/*where b.Month in (202311, 202310, 202309, 202308)*/;
quit;

My code runs without any error when I remove the Month but when I add it, it does not produce any data. Could you please help?

[PaigeMiller]

Please think this through. If your code is joining data sets Aug_Nov_check and FSFINREP.FINREP_LM_AF_DET, and it is not working, then what information would we need to help you figure out why it is not working?

[Sandeep77]

Here is the log when I include the where clause ( which does not produce any data):

NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27
28 Proc sql;
29 create table Test as
30 select a.ACCOUNT_ID,
31 a.FINREP_CURR_SEGMENT,
32 a.FINREP_CURR_SEGMENT_REASON,
33 a.DPD,
34 a.DRAWN_EXPOSURE,
35 b.Month
36 from Aug_Nov_check as a
37 inner join FSFINREP.FINREP_LM_AF_DET as b on a.ACCOUNT_ID = b.ACCOUNT_ID
38 where b.Month in (202311, 202310, 202309, 202308);
NOTE: Table WORK.TEST created, with 0 rows and 6 columns.

39 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 3.40 seconds
user cpu time 0.02 seconds
system cpu time 0.02 seconds
memory 5298.21k
OS Memory 40624.00k
Timestamp 01/23/2024 12:43:19 PM
Step Count 48 Switch Count 8
Page Faults 4
Page Reclaims 105
Page Swaps 0
Voluntary Context Switches 121
Involuntary Context Switches 1
Block Input Operations 0
Block Output Operations 0

[PaigeMiller]

Incorrect answer. You have proved to us that the join fails; but we still don't know why.

The answer is that we need to know what is in these two data sets, so we can see why the merge is failing. Please show us portions of these two data sets. Please, we have discussed this before, I am going to insist now that you follow these instructions and run the DATA2DATASTEP macro there, and not provide portions of the data sets via other methods. Repeating: I insist that you provide the data using the DATA2DATASTEP at these instructions. I do not ask this to be annoying; I ask this so that you will provide all the necessary information we need to figure out what the problem is. You will get faster and better answers by doing this. Using this macro helps you as well as us.

[Kurt_Bremser]

Are you sure your month values are stored like this, and not as real SAS dates? What does PROC CONTENTS say about the variable?

[Sandeep77]

Hi, Proc contents shows:
Variable = Month,
Type = Num,
Len = 8,
format = YYMMN6.,
Informat = Blank

[Kurt_Bremser · Accepted solution]

Which means the values are counts of days starting at 1960-01-01, so your values in the IN list cannot work.

Usually, such values would be set to the first of the month, so a literal of

'01sep3023'd

would work. Or you convert to a string with the same format:

where put(b.month,yymmn6.) in ("202311","202310","202309","202308")

[Sandeep77]

Thanks, that worked!!