BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Sandeep77
Lapis Lazuli | Level 10

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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

View solution in original post

7 REPLIES 7
PaigeMiller
Diamond | Level 26

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?

--
Paige Miller
Sandeep77
Lapis Lazuli | Level 10

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
Diamond | Level 26

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.

--
Paige Miller
Sandeep77
Lapis Lazuli | Level 10
Hi, Proc contents shows:
Variable = Month,
Type = Num,
Len = 8,
format = YYMMN6.,
Informat = Blank
Kurt_Bremser
Super User

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
Lapis Lazuli | Level 10
Thanks, that worked!!

SAS Innovate 2025: Call for Content

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!

Submit your idea!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 1089 views
  • 3 likes
  • 3 in conversation