DATA Step, Macro, Functions and more

Need SAS query help for date/month based processing?

Posts: 46

Need SAS query help for date/month based processing?

I need a query help for the following requirement. I have a dataset with 4 variables that looks like the following:

id	date	charge_mode	code_no
a1	2-Oct-15	5	127
a1	5-Oct-15	5	104
a1	5-Oct-15	5	510
a1	5-Oct-15	5	511
a1	1-Nov-15	5	128
a1	5-Nov-15	5	330
a2	2-Oct-15	6	430
a2	5-Oct-15	6	230
a2	5-Oct-15	6	128
a2	5-Oct-15	6	127
a2	1-Nov-15	6	520
a2	5-Nov-15	6	210

This is a transaction data for each custid, I need to find if any of transactions that has the code_no(127,128,104) which contributes to flag1 and flag2 variables that I need to create for each "month" of a customer id.

The monthly cycle(/*most important*/) depends on the variable charge_mode, like in the above example the chargemode for id a1 is 5 and a2 is 6, meaning : 
1.	For A1,5th of oct to 4th of nov is the period to be considered for October month and 5th of Nov to 4th of Dec to be considered for November and so on.
2.	For A2, the period would be from 6th to  5th and so on.
The logic for flags:
If in a month, all the 3 code_no(127,128,104) exist then flag_2=1 and flag_1=1;
if in a month, only 104 exist, then flag_2=1,flag_1=1 If in a month, only (127 and 128) exist, and if 128 is the latest found record for the month, then flag_1=0,flag_2=0 If in a month, only (127 and 128) exist and if 127 is the latest found for the month, then flag_1=1, flag_2=0 1. If only 127 is there , then flag_1=1, flag_2=0
2. if only 128, exists, then flag_1=0. flag_2=0 Want should be in the following format: id flag1 flag2 month a1 0 1 oct a1 1 0 nov a1 0 0 dec followed a2 and so on....…..
Super User
Posts: 6,933

Re: Need SAS query help for date/month based processing?

Ups Smiley Happy

I can see why you posted that question again.

But you could have used the "Edit" option from the options menu (the lttle spokewheel) to correct the layout.

Maxims of Maximally Efficient SAS Programmers
Posts: 46

Re: Need SAS query help for date/month based processing?

@KurtBremser  Sir, Thank you for you time and response. Yes, I agree it's not nice to duplicate questions but you have noticed the difference.  I apologise that I duplicated it because i couldn't find the edit in my laptop screen for some weird reason. Forgive me for that. 

Yep, you were right about the want values. Let me test your code and clarify my question clearly and sincerely and follow up with any doubts if i have. If you allow me, I will mark your answer as solution soon as I complete my testing bcoz I would like to take this chance to keep it open to have a follow up clarification with you. Thank you once again Smiley Happy


Ask a Question
Discussion stats
  • 2 replies
  • 2 in conversation