I have a dataset which shows data from January 22 to December 22. Rest of the data I have classified as others. But when I try to group it month wise, it also shows the others. I want SAS to read only Jan to Dec 22 information and ignore others. Can you please help what I can do? Below is the sample data and the code:
Proc SQL;
create table Monthlytraces as
select Month, count (Month) as No_of_monthly_traces
from custidsort
group by Month;
quit;
icustomerid | debt_code | Balance | client_code | op_code | tran_code | TPT_Date | Trace_result | POS | Month |
. | 100648997 | 0 | BARCCARD193 | AW29 | MO9745 | 07SEP2022:11:22:14.000 | LAS | 1 | Sep-22 |
. | 102411204 | 0 | 3G023 | LJ1 | MO9748 | 01SEP2022:15:18:53.000 | NEG | 0 | Sep-22 |
. | 10416544 | 0 | CAPONE004 | LJ1 | MO9748 | 26APR2022:14:45:25.000 | NEG | 0 | Apr-22 |
. | 104171756 | 0 | HSBC024 | DH3 | MO9746 | 13SEP2019:13:50:49.000 | NEG | 0 | Other |
. | 107306946 | 0 | ORANGE001 | AW29 | MO9745 | 07SEP2022:09:36:37.000 | LAS | 1 | Sep-22 |
I don't see anything in the code that you show that resembles "Rest of the data I have classified as others. "
Are you sure that is applied to data set Custidsort? If so, you should show how.
Maybe
Proc SQL;
create table Monthlytraces as
select Month, count (Month) as No_of_monthly_traces
from (select * from custidsort where Year(datepart(tpt_date))=2022 )
group by Month;
quit;
Note that from the "data" you show we really don't know if that "month" variable is a date or string or what.
Please provide data as working SAS data step code, as you have done in the past.
I don't see anything in the code that you show that resembles "Rest of the data I have classified as others. "
Are you sure that is applied to data set Custidsort? If so, you should show how.
Maybe
Proc SQL;
create table Monthlytraces as
select Month, count (Month) as No_of_monthly_traces
from (select * from custidsort where Year(datepart(tpt_date))=2022 )
group by Month;
quit;
Note that from the "data" you show we really don't know if that "month" variable is a date or string or what.
What type of variable if MONTH? Is it numeric or character? If it is numeric are the range values it contains? A what format is being used so that it is displaying the values you show?
If you want exclude some observations use a WHERE clause. So if MONTH is a character variable use:
create table Monthlytraces as
select Month, count (Month) as No_of_monthly_traces
from custidsort
where month ne 'Other'
group by Month
;
Thank you. I got it. I was looking for 'where month ne other'. It worked.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.