Desktop productivity for business analysts and programmers

How to select 3 months with different transactin amount

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

How to select 3 months with different transactin amount

Dear all,

I stuck in one issue, I have dataset which is having 3 months, but I need to select customer data who have different  transaction data.

 

Example

IF First month 60 and second month 60 and third month 60.....> Then to be delete

IF First month 60 and second month 50 and third month 60.....> Then to be delete

IF First month 60 and second month 50 and third month 40.....> Then to be Include.

 

Please help on this issue.


Accepted Solutions
Solution
‎04-09-2018 06:03 AM
Occasional Contributor
Posts: 7

Re: How to select 3 months with different transactin amount

Posted in reply to KurtBremser

Dear KurtBremser,

 

Excellent.Thank you very much.

 

 

Regards,

Dhruva.

View solution in original post


All Replies
Frequent Contributor
Posts: 80

Re: How to select 3 months with different transactin amount

Posted in reply to dhruvakumar

Hi Dhruv,

 

use intck function to calculated no of days or no of month between tow  transaction date.

 

no_of_day = intck("days",'01jan18'd,'01apr18'd) ---> will show the no of days

 

no_of_day = intck("MONTH",'01jan18'd,'01apr18'd)  ----> will show the no of month

 

Occasional Contributor
Posts: 7

Re: How to select 3 months with different transaction...

Posted in reply to dhruvakumar

Hi Tarun,

 

thanks for reply. Here my requirement is not to delete months. In the above example 60 is not number of days, it transaction amount.

In the first instance all 3 months have 60 so ,it will not be select, same as second which 60 is repeated in twice.. so it would be not select.

Last instance.. it have three different transaction amount so it would be include.

 

Thanks!

Frequent Contributor
Posts: 80

Re: How to select 3 months with different transaction...

Posted in reply to dhruvakumar

WANT UNIQUE TRANSACTION MONTH ?

Super User
Posts: 9,611

Re: How to select 3 months with different transactin amount

Posted in reply to dhruvakumar

It is always a VERY GOOD IDEA to supply example data that illustrates the issue and which can be used for testing. The proper method for this is posting a data step with datalines. A macro that does the conversion of a dataset to a data step can be found here, and the proper method for posting code so that the formatting is preserved can be found here.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 13,084

Re: How to select 3 months with different transactin amount

Posted in reply to dhruvakumar

Provide example input data.

 

Provide example desired result. Data is best provided in the form of data steps so we know variable names and types.

 

Post question requirements in terms of those named variables in your data set.

You may know what

IF First month 60 and second month 60 and third month 60.....> 

means but without data, variables and explanations of how you determine a value is first month and what ever the heck 60 is we can't help much. And are you interested in exactly 60, greater than 60, less than 60, greater than or equal to 60 or less than or equal to 60???

 

Occasional Contributor
Posts: 7

Re: How to select 3 months with different transactin amount

Dear All,

 

Thank you.As mentioned if transaction data :

 

IF First month 60 and second month 60 and third month 60.... then it should be not select.

 

Only the transaction if First month 60 and second month 50 and third month 40... will be select from dataset. Means transaction data should be distinct .. they only data should be select in the query.

Example :

BillTxn_Datenewcin
6004/01/2018EMP1001
6004/02/2018EMP1001
6004/03/2018EMP1001
4004/01/2018EMP1002
5004/02/2018EMP1002
3004/03/2018EMP1002


In the above example only EMP1002  should be select,because  it is having distinct bill amount 30,40,50.

 

The other EMP1002   should not select, because it is having the same bill amount 60,60,60

 

Here my requirement is need to select only EMP1002 records only.

 

Thanks,

Dhruva,

 

 

Super User
Posts: 9,611

Re: How to select 3 months with different transactin amount

Posted in reply to dhruvakumar

Here is a quick example of what we mean by "post datasets in a data step":

data have;
input bill txn_date :mmddyy10. newcin $;
cards;
60 04/01/2018 EMP1001
60 04/02/2018 EMP1001
60 04/03/2018 EMP1001
40 04/01/2018 EMP1002
50 04/02/2018 EMP1002
30 04/03/2018 EMP1002
;
run;

Please follow this example in the future, as it makes creating and testing code that much easier.

 

A solution SQL looks like this:

proc sql;
select * from have
where newcin in (
  select newcin
  from have
  group by newcin
  having count(distinct bill) > 1
)
;
quit;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Solution
‎04-09-2018 06:03 AM
Occasional Contributor
Posts: 7

Re: How to select 3 months with different transactin amount

Posted in reply to KurtBremser

Dear KurtBremser,

 

Excellent.Thank you very much.

 

 

Regards,

Dhruva.

Respected Advisor
Posts: 4,557

Re: How to select 3 months with different transactin amount

Posted in reply to dhruvakumar

@dhruvakumar

If @KurtBremser answer is the solution for you then please mark it as solution. This is your way to say "thank you" and it also shows your question as answered on the overview page.

Occasional Contributor
Posts: 7

Re: How to select 3 months with different transactin amount

Hi Patrick,

Thank you for advise.Done.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 244 views
  • 1 like
  • 5 in conversation