BookmarkSubscribeRSS Feed
Allaluiah
Quartz | Level 8
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....…..
2 REPLIES 2
Kurt_Bremser
Super User

Ups 🙂

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.

Allaluiah
Quartz | Level 8

@Kurt_Bremser  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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 705 views
  • 0 likes
  • 2 in conversation