Adding new column using case when statement

[Sandeep77]

Hi all,

I have a created a dataset which has dates and I am trying to categorize all the data month wise and using a case when statement for that. But when I am using the new code it says the following columns were not found in the contributing tables: month. Can you please check and let me know what is the mistake I am doing here? Thanks

Data Monthwise;
input 
rep_code	debt_code	FV	tx_date	tran_code;
Datalines;
005H	419266408	115.53	27MAR2023:00:00:00.000	MO3365
005H	269652723	382.46	03DEC2022:00:00:00.000	MO3365
005H	320927163	121.87	01DEC2022:00:00:00.000	MO3365
005H	327014254	108.63	15DEC2022:00:00:00.000	MO3365
005H	329818645	604.63	09NOV2022:00:00:00.000	MO3365
;
run;

Proc sql;
create table Adding_month as
select *,
case
when tx_date between '01OCT2022'd and '31OCT2022'd then month = 'Oct2022'
when tx_date between '01NOV2022'd and '30NOV2022'd then month = 'Nov2022'
when tx_date between '01DEC2022'd and '31DEC2022'd then month = 'Dec2022'
when tx_date between '01JAN2023'd and '31JAN2023'd then month = 'Jan2023'
when tx_date between '01FEB2023'd and '28FEB2023'd then month = 'Feb2023'
when tx_date between '01MAR2023'd and '31MAR2023'd then month = 'Mar2023'
end as Month
from All_accounts;
quit;

Error log:
29         Proc sql;
30         create table Adding_month as
31         select *,
32         case
33         when tx_date between '01OCT2022'd and '31OCT2022'd then month = 'Oct2022'
34         when tx_date between '01NOV2022'd and '30NOV2022'd then month = 'Nov2022'
35         when tx_date between '01DEC2022'd and '31DEC2022'd then month = 'Dec2022'
36         when tx_date between '01JAN2023'd and '31JAN2023'd then month = 'Jan2023'
37         when tx_date between '01FEB2023'd and '28FEB2023'd then month = 'Feb2023'
38         when tx_date between '01MAR2023'd and '31MAR2023'd then month = 'Mar2023'
39         end as Month
40         from All_accounts;
NOTE: A CASE expression has no ELSE clause. Cases not accounted for by the WHEN clauses will result in a missing value for the CASE 
      expression.
ERROR: The following columns were not found in the contributing tables: month.
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
41         quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              5842.56k
      OS Memory           33376.00k
      Timestamp           04/14/2023 10:11:24 AM
      Step Count                        15  Switch Count  0
      
42         
43         %LET _CLIENTTASKLABEL=;
2                                                          The SAS System                             13:45 Thursday, April 13, 2023

44         %LET _CLIENTPROCESSFLOWNAME=;
45         %LET _CLIENTPROJECTPATH=;
46         %LET _CLIENTPROJECTPATHHOST=;
47         %LET _CLIENTPROJECTNAME=;
48         %LET _SASPROGRAMFILE=;
49         %LET _SASPROGRAMFILEHOST=;
50         
51         ;*';*";*/;quit;run;
52         ODS _ALL_ CLOSE;
53         
54         
55         QUIT; RUN;

[PaigeMiller]

Don't do this:

Proc sql;
create table Adding_month as
select *,
case
when tx_date between '01OCT2022'd and '31OCT2022'd then month = 'Oct2022'
when tx_date between '01NOV2022'd and '30NOV2022'd then month = 'Nov2022'
when tx_date between '01DEC2022'd and '31DEC2022'd then month = 'Dec2022'
when tx_date between '01JAN2023'd and '31JAN2023'd then month = 'Jan2023'
when tx_date between '01FEB2023'd and '28FEB2023'd then month = 'Feb2023'
when tx_date between '01MAR2023'd and '31MAR2023'd then month = 'Mar2023'
end as Month
from All_accounts;
quit;

Instead, assign a format to the variable, this can be done in a data step, one or two lines of code is all it takes rather than the very tedious SQL code which requires a line for each month. In a data step

tx_date=datepart(tx_date);
format tx_date monyy7.;

Furthermore, @Sandeep77 , I'm sure we have mentioned this to you before, but do not provide us with DATA step code which doesn't work! This code cannot work, you need to fix it before you provide it to us:

Data Monthwise;
input 
rep_code	debt_code	FV	tx_date	tran_code;
Datalines;
005H	419266408	115.53	27MAR2023:00:00:00.000	MO3365
005H	269652723	382.46	03DEC2022:00:00:00.000	MO3365
005H	320927163	121.87	01DEC2022:00:00:00.000	MO3365
005H	327014254	108.63	15DEC2022:00:00:00.000	MO3365
005H	329818645	604.63	09NOV2022:00:00:00.000	MO3365
;

[PaigeMiller]

Sadly, in my opinion, the answer from @MayurJadhav is marked correct, even though later @MayurJadhav indicates that formats would be a better approach.

@Sandeep77 another reason not to create a new column is that your new column of month names such as OCT2022 will not sort properly. They will sort alphabetically. You will find that this column of month names will have APR2023 as the first month when you do a sort, AUG2023 as the second month, and so on; and I'm pretty sure that is not what you or anyone else wants. If you use formats, this isn't a problem, the values will sort properly.

[Sandeep77]

Thank you. I tried this method for the first time and it is definitely a better way to do. Thanks again.

[MayurJadhav · Accepted solution]

Hi, @Sandeep77 

You don't need to mention "month=" in CASE statement.

You can try this.

Data Monthwise;
input rep_code $ debt_code $ FV $ tx_date :datetime25.5 tran_code $;
format tx_date datetime25.5;
Datalines;
005H 419266408 115.53 27MAR2023:00:00:00.000 MO3365
005H 269652723 382.46 03DEC2022:00:00:00.000 MO3365
005H 320927163 121.87 01DEC2022:00:00:00.000 MO3365
005H 327014254 108.63 15DEC2022:00:00:00.000 MO3365
005H 329818645 604.63 09NOV2022:00:00:00.000 MO3365
;
run;

Proc sql;
create table Adding_month as
select *,
case
when datepart(tx_date) between '01OCT2022'd and '31OCT2022'd then 'Oct2022'
when datepart(tx_date) between '01NOV2022'd and '30NOV2022'd then 'Nov2022'
when datepart(tx_date) between '01DEC2022'd and '31DEC2022'd then 'Dec2022'
when datepart(tx_date) between '01JAN2023'd and '31JAN2023'd then 'Jan2023'
when datepart(tx_date) between '01FEB2023'd and '28FEB2023'd then 'Feb2023'
when datepart(tx_date) between '01MAR2023'd and '31MAR2023'd then 'Mar2023'
end as Month
from Monthwise;
quit;

[PaigeMiller]

I would still say not to do this in SQL. Use formats, then you don't need to type a row for each month in the data set.

[MayurJadhav]

I totally agree!