Where function is not showing the desired result

[Sandeep77]

Hi Experts,

I have a sample dataset which includes a month column which has month like YYYYMM format ( e.g. 201911 is November 2019). I have written a code which runs successfully but does not generate any output data. Could you please check and help? (Month column has type as 'Numeric', Group as 'Date' and Format as YYMMN6.)

Sample dataset

Data Accounts_in_Oct;
infile cards expandtabs;
input ACCOUNT_ID FINREP_CURR_SEGMENT$ FINREP_CURR_SEGMENT_REASON$ Month;
datalines ;
100016 NONPERFORMING MEETREQUIREMENTS 201911
100032 PERFORMING MEETREQUIREMENTS 202311
100040 PERFORMING MEETREQUIREMENTS 202310
100075 NONPERFORMING MEETREQUIREMENTS 201911
100112 PERFORMING MEETREQUIREMENTS 201911
100120 PERFORMING MEETREQUIREMENTS 201911
100163 NONPERFORMING MEETREQUIREMENTS 202310
100198 NONPERFORMING MEETREQUIREMENTS 201911
100200 PERFORMING MEETREQUIREMENTS 201911
100278 NONPERFORMING MEETREQUIREMENTS 202310
100286 PERFORMING MEETREQUIREMENTS 201911
;
run;

Proc sql;
create table Accounts_in_Oct as 
select 
ACCOUNT_ID,
FINREP_CURR_SEGMENT,
FINREP_CURR_SEGMENT_REASON,
month
from FSFINREP.FINREP_LM_AF_DET
where month = 201911;
quit;
Log:
28         Proc sql;
29         create table Accounts_in_Oct as
30         select
31         ACCOUNT_ID,
32         FINREP_CURR_SEGMENT,
33         FINREP_CURR_SEGMENT_REASON,
34         month
35         from FSFINREP.FINREP_LM_AF_DET
36         where month = 201911;
NOTE: Table WORK.ACCOUNTS_IN_OCT created, with 0 rows and 4 columns.

37         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           10.96 seconds
      user cpu time       0.02 seconds
      system cpu time     0.01 seconds
      memory              5004.75k
      OS Memory           31404.00k
      Timestamp           12/13/2023 02:54:42 PM
      Step Count                        35  Switch Count  8
      Page Faults                       0
      Page Reclaims                     4
      Page Swaps                        0
      Voluntary Context Switches        77
      Involuntary Context Switches      4
      Block Input Operations            0
      Block Output Operations           0
      

[PaigeMiller]

What is your question really? Is your question: does the SQL code work on the sample data set? Or is your question: does the SQL code read from FSFINREP.FINREP_LM_AF_DET and produce the sample data set?

It works fine on your sample data, if you make some important  changes to refer to the proper data set

Proc sql;
create table want as 
select 
ACCOUNT_ID,
FINREP_CURR_SEGMENT,
FINREP_CURR_SEGMENT_REASON,
month
from accounts_in_oct
where month = 201911;
quit;

I have no idea what is in data set FSFINREP.FINREP_LM_AF_DET, so I can't tell if it would work on that as well.

Also, please do not provide conflicting information. You say that MONTH has format YYMMN6., but in your sample data set it does not have that format, it is an integer, unformatted. Perhaps this is your problem, but we can't tell until you clear this up.

[yabwon]

strange, works for me:

1    Data Accounts_in_Oct;
2    infile cards expandtabs;
3    input ACCOUNT_ID FINREP_CURR_SEGMENT$ FINREP_CURR_SEGMENT_REASON$ Month;
4    datalines ;

NOTE: The data set WORK.ACCOUNTS_IN_OCT has 11 observations and 4 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


16   ;
17   run;
18
19   Proc sql;
20   create table Accounts_in_Oct as
21   select
22   ACCOUNT_ID,
23   FINREP_CURR_SEGMENT,
24   FINREP_CURR_SEGMENT_REASON,
25   month
26   from Accounts_in_Oct
27   where month = 201911;
WARNING: This CREATE TABLE statement recursively references the target table. A consequence of this
         is a possible data integrity problem.
NOTE: Table WORK.ACCOUNTS_IN_OCT created, with 7 rows and 4 columns.

28   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Bart

[ballardw]

Most likely cause would be no values matching the where clause.

Show us the output of running this:

Proc freq data=FSFINREP.FINREP_LM_AF_DET;
tables month;
run;

I would be kind of suspicious that perhaps you intended month = 201910 if you actually wanted values in October unless you "month" variable is even worse then that sort of value is typically.

[PaigeMiller]

---

@ballardw wrote:

Most likely cause would be no values matching the where clause.

Show us the output of running this:

Proc freq data=FSFINREP.FINREP_LM_AF_DET;
tables month;
run;

I would be kind of suspicious that perhaps you intended month = 201910 if you actually wanted values in October unless you "month" variable is even worse then that sort of value is typically.

---

This won't help if MONTH is formatted. The original post indicated it was, but the data provided indicated it was not formatted. In other words, a contradiction. We don't know the real information until the OP clears this up for us.

Anyway, PROC CONTENTS would help.

[Tom]

Looks like you do not have any data from October 2512.  And why would the day of month be coded as 24 instead of 1?  Normally when you store dates that are displayed showing only the year and month you use the first day of the month.

812  data _null_;
813    month=201911;
814    put (3*month) (=date9. =yymmdd10. =comma10.);
815  run;

month=24OCT2512 month=2512-10-24 month=201,911

[Kurt_Bremser]

WHERE is not a function. It is either a dataset option, a statement (in DATA and PROC steps), or a clause in SQL.

To have a WHERE function, you would have to create it yourself with PROC FCMP.

[Sandeep77]

Sorry for the delayed response. The sample data that I provided it runs alright but when I do the same in my original data, it does not give any information in the output data. In the original dataset (FINREP_LM_AF_DET), the month is in YYMMN6. Format. I don't know whether it has to do something with the format. My question is why does my code does not read the where clause in my code?

Proc sql;
create table Finrep as 
select 
ACCOUNT_ID,
FINREP_CURR_SEGMENT,
FINREP_CURR_SEGMENT_REASON,
month
from FSFINREP.FINREP_LM_AF_DET
where month = 202310 /* If I add the month as 202310, it does not generate any data in the output. If I remove the where clause, it shows that it has data from 201910 to 202310 */
;
quit;

[Patrick · Accepted solution]

Try below code and let us know the outcome.

Proc sql;
  create table Finrep as 
    select 
      ACCOUNT_ID,
      FINREP_CURR_SEGMENT,
      FINREP_CURR_SEGMENT_REASON,
      month
    from FSFINREP.FINREP_LM_AF_DET
    where put(month,yymmn6.) = '202310' 
  ;
quit;

[Sandeep77]

Genius mind! That worked. Thanks!!

[PaigeMiller]

@Sandeep77 

We have discussed many times the importance of providing sample data correctly. All of these issues in this thread and many other of your threads would go away if you used these instructions to provide us with sample data (which we have also discussed). It is well worth your time to learn how to do this, and then you will get faster and better answers with much less confusion.

[Patrick]

@Sandeep77 Do you understand why this code is working and what you've missed in your attempt?

What SAS Date, DateTime and Time values are and how they relate to formats is a very important concept one needs to understand. 

Happy to give you some pointers in case you don't know yet.