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

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
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26

Please provide data as working SAS data step code, as you have done in the past.

--
Paige Miller
ballardw
Super User

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.

Tom
Super User Tom
Super User

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
;
Sandeep77
Lapis Lazuli | Level 10

Thank you. I got it. I was looking for 'where month ne other'. It worked.

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

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
  • 4 replies
  • 713 views
  • 2 likes
  • 4 in conversation