SAS Tricky Query

[BaalaRaaji]

Thank you Scott..you both are have amazing SAS Code Skills,,

Much Appreciated..

I will review the code but thanks again..:-)

[BaalaRaaji]

Hi Scott...Amazing..

I have run two codes and I have got same output as I have checked for only one account.

Agree that your code is very easy to understand at one instance.

I have learned logic to update by LOCF..thats very cool..

Thank you..Both....Much appreciated Cheers!

[novinosrin]

Good morning @BaalaRaaji   , @ScottBass  et al  Some hash fun after all

data have;
infile cards truncover;
input COLL_IDNN_HK $ 	MAT_CLREF	END_OF_MONTH :date9.	ACT_CODE $;
format END_OF_MONTH :date9.;
cards;
2CD3B	101	31-Jan-10	
2CD3B	101	28-Feb-10	
2CD3B	101	31-Mar-10	
2CD3B	101	30-Apr-10	
2CD3B	101	31-May-10	
2CD3B	101	30-Jun-10	
2CD3B	101	31-Jul-10	
2CD3B	101	31-Aug-10	
2CD3B	101	30-Sep-10	
2CD3B	101	31-Oct-10	
2CD3B	101	30-Nov-10	
2CD3B	101	31-Dec-10	
2CD3B	101	31-Jan-11	
2CD3B	101	28-Feb-11	
2CD3B	101	31-Mar-11	
2CD3B	101	30-Apr-11	
2CD3B	101	31-May-11	
2CD3B	101	30-Jun-11	
2CD3B	101	31-Jul-11	
2CD3B	101	31-Aug-11	
2CD3B	101	30-Sep-11	
2CD3B	101	31-Oct-11	
2CD3B	101	30-Nov-11	
2CD3B	101	31-Dec-11	
2CD3B	101	31-Jan-12	
2CD3B	101	29-Feb-12	
2CD3B	101	31-Mar-12	
2CD3B	101	30-Apr-12	
2CD3B	101	31-May-12	
2CD3B	101	30-Jun-12	
2CD3B	101	31-Jul-12	
2CD3B	101	31-Aug-12	
2CD3B	101	30-Sep-12	
2CD3B	101	31-Oct-12	
2CD3B	101	30-Nov-12	
2CD3B	101	31-Dec-12	
2CD3B	101	31-Jan-13	
2CD3B	101	28-Feb-13	
2CD3B	101	31-Mar-13	
2CD3B	101	30-Apr-13	
2CD3B	101	31-May-13	
2CD3B	101	30-Jun-13	
2CD3B	101	31-Jul-13	
2CD3B	101	31-Aug-13	
2CD3B	101	30-Sep-13	
2CD3B	101	31-Oct-13	
2CD3B	101	30-Nov-13	
2CD3B	101	31-Dec-13	
2CD3B	101	31-Jan-14	
2CD3B	101	28-Feb-14	
2CD3B	101	31-Mar-14	
2CD3B	101	30-Apr-14	
2CD3B	101	31-May-14	
2CD3B	101	30-Jun-14	
2CD3B	101	31-Jul-14	
2CD3B	101	31-Aug-14	
2CD3B	101	30-Sep-14	
2CD3B	101	31-Oct-14	
2CD3B	101	30-Nov-14	
2CD3B	101	31-Dec-14	
2CD3B	101	31-Jan-15	
2CD3B	101	28-Feb-15	
2CD3B	101	31-Mar-15	
2CD3B	101	30-Apr-15	
2CD3B	101	31-May-15	
2CD3B	101	30-Jun-15	
2CD3B	101	31-Jul-15	
2CD3B	101	31-Aug-15	
2CD3B	101	30-Sep-15	
2CD3B	101	31-Oct-15	
2CD3B	101	30-Nov-15	
2CD3B	101	31-Dec-15	
2CD3B	101	31-Jan-16	
2CD3B	101	29-Feb-16	
2CD3B	101	31-Mar-16	
2CD3B	101	30-Apr-16	
2CD3B	101	31-May-16	
2CD3B	101	30-Jun-16	
2CD3B	101	31-Jul-16	
2CD3B	101	31-Aug-16	
2CD3B	101	30-Sep-16	
2CD3B	101	31-Oct-16	
2CD3B	101	30-Nov-16	
2CD3B	101	31-Dec-16	
2CD3B	101	31-Jan-17	
2CD3B	101	28-Feb-17	
2CD3B	101	31-Mar-17	
2CD3B	101	31-Mar-17	psale
2CD3B	101	30-Apr-17	
2CD3B	101	31-May-17	
2CD3B	101	30-Jun-17	
2CD3B	101	31-Jul-17	
2CD3B	101	31-Aug-17	
2CD3B	101	31-Aug-17	sumap
2CD3B	101	30-Sep-17	
2CD3B	101	31-Oct-17	
2CD3B	101	31-Oct-17	sumu
2CD3B	101	30-Nov-17	sumsv
2CD3B	101	30-Nov-17	
2CD3B	101	31-Dec-17	
2CD3B	101	31-Jan-18	
2CD3B	101	28-Feb-18	
2CD3B	101	28-Feb-18	judct
2CD3B	101	31-Mar-18	judo
2CD3B	101	31-Mar-18	
2CD3B	101	30-Apr-18	
2CD3B	101	31-May-18	
2CD3B	101	31-May-18	ntva
2CD3B	101	30-Jun-18	vlreq
2CD3B	101	30-Jun-18	poss
2CD3B	101	30-Jun-18	biarr
2CD3B	101	30-Jun-18	
2CD3B	101	31-Jul-18	
2CD3B	101	31-Jul-18	vrec
2CD3B	101	31-Aug-18	propr
2CD3B	101	31-Aug-18	nsold
2CD3B	101	31-Aug-18	
2CD3B	101	30-Sep-18	
2CD3B	101	30-Sep-18	soldc
2CD3B	101	30-Sep-18	soldp
2CD3B	101	31-Oct-18	
2CD3B	101	30-Nov-18	
2CD3B	101	30-Nov-18	post
2CD3B	101	30-Nov-18	msset
2CD3B	101	30-Nov-18	woffa
2CD3B	101	30-Nov-18	fin
2CD3B	101	31-Dec-18	
2CD3B	101	31-Jan-19	
2CD3B	101	28-Feb-19	
2CD3B	101	31-Mar-19	
2CD3B	101	30-Apr-19	
;


data want;
if _n_=1 then do;
 dcl hash H () ;
   h.definekey  ("COLL_IDNN_HK","MAT_CLREF") ;
   h.definedata ("_ACT_CODE") ;
   h.definedone () ;
end;
do until(last.MAT_CLREF);
set have;
by COLL_IDNN_HK MAT_CLREF;
_act_code=act_code;
if ACT_CODE>' ' then _rc=h.replace();
end;
do until(last.MAT_CLREF);
update have(obs=0) have;
by COLL_IDNN_HK MAT_CLREF;
_rc=h.find();
if _flag=1 then call missing(act_code);
if act_code>' ' then output;
if _rc=0 and act_code=_act_code then _flag=1;
end;
drop _:;
run;

[BaalaRaaji]

Thank you so much...Really Much Appreciated..

I have learned different ways of writing the codes..

I don't understand the hash keys but your code is very easy to follow and pick it up..

Thank you again..

Cheers!

[BaalaRaaji]

Hi there, @novinosrin  and @ScottBass 

I am stuck with other logic..

Please find attached sheet for reference..COLUMN E,F,G (we need to create the logic)

Base date is from A –D column.

RS	COLL_IDNN_HK	ACT_ALLOC	ACT_CODE
1	000157A9F6ACCA2E6869F8E688B2CD3B	08Jun2018	poss
2	000157A9F6ACCA2E6869F8E688B2CD3B	12Jun2018	poss
3	000157A9F6ACCA2E6869F8E688B2CD3B	25Jun2018	biarr
4	000157A9F6ACCA2E6869F8E688B2CD3B	18Jul2018	propr
5	000157A9F6ACCA2E6869F8E688B2CD3B	17Aug2018	propr
6	000668681CA9E8C67004DC166EE8AF3A	27Aug2012	poss
7	000668681CA9E8C67004DC166EE8AF3A	25Sep2012	adset
8	000668681CA9E8C67004DC166EE8AF3A	24Oct2012	resve
9	000668681CA9E8C67004DC166EE8AF3A	07Nov2012	biarr
10	000C0952A62BE28AFD818F70F5C52E9A	01Aug2017	bvsap
11	001427B3AAEF4D8084166321501D17C0	12Mar2012	poss
12	001427B3AAEF4D8084166321501D17C0	05Jul2012	bvsap
13	001427B3AAEF4D8084166321501D17C0	19Oct2012	biarr

For each Hash Key(COLL_IDNN_HK) I need to create the Effective date and Expiry date and assign the flags.

Effective_date is the current act_alloc_date

Expiry_date is the next observation act_alloc_date

For last observation by HK I need to assign the expiry _date as 31DEC9999.

For Flags..Below list of act codes..

If act_code = 'bvsap' then act_status_flag= 'bvsap';

else if act_code in('poss' 'biarr' 'propr' 'adset' 'resve') then act_status_flag= 'poss';

Below Final output:

RS	COLL_IDNN_HK	ACT_ALLOC	ACT_CODE	eff_date(Min)	Expiry_date(Max)	flags
1	000157A9F6ACCA2E6869F8E688B2CD3B	08Jun2018	poss	08Jun2018	12Jun2018	poss
2	000157A9F6ACCA2E6869F8E688B2CD3B	12Jun2018	poss	12Jun2018	25Jun2018	poss
3	000157A9F6ACCA2E6869F8E688B2CD3B	25Jun2018	biarr	25Jun2018	18Jul2018	poss
4	000157A9F6ACCA2E6869F8E688B2CD3B	18Jul2018	propr	18Jul2018	17Aug2018	poss
5	000157A9F6ACCA2E6869F8E688B2CD3B	17Aug2018	propr	17Aug2018	31Dec9999	poss
6	000668681CA9E8C67004DC166EE8AF3A	27Aug2012	poss	27Aug2012	25Sep2012	poss
7	000668681CA9E8C67004DC166EE8AF3A	25Sep2012	adset	25Sep2012	24Oct2012	poss
8	000668681CA9E8C67004DC166EE8AF3A	24Oct2012	resve	24Oct2012	07Nov2012	poss
9	000668681CA9E8C67004DC166EE8AF3A	07Nov2012	biarr	07Nov2012	31Dec9999	poss
10	000C0952A62BE28AFD818F70F5C52E9A	01Aug2017	bvsap	01Aug2017	31Dec9999	bvsap
11	001427B3AAEF4D8084166321501D17C0	12Mar2012	poss	12Mar2012	05Jul2012	poss
12	001427B3AAEF4D8084166321501D17C0	05Jul2012	bvsap	05Jul2012	19Oct2012	bvsap
13	001427B3AAEF4D8084166321501D17C0	19Oct2012	biarr	19Oct2012	31Dec9999	poss

Once I get this data..i have to join with the end of month data since 2010 to 2019..thats different..i can come back later for that query.:-) if needed.

Appreciate your help..thank you

[ScottBass]

Sorry for the tone but here comes the blast...

1) You're still posting screenshots instead of a self-contained data step, forcing me to convert your screenshot into code I can work with.  And some of the screenshots are cut off.  Did you learn nothing from my very first reply to your original post?

2) And this trickle feed of ever changing requirements is very tiresome.  We provide you a spoon-fed solution to your question(s), only to have you change the requirements.  You really need to learn how to ask an effective question, providing all the relevant information up front.

3) All the "you're wonderful", "amazing code", "hello 'team' " comments are falling on deaf ears (at least mine).  None of these code approaches are "amazing", you just need to learn more SAS.  Or how to do a Google search.

Tell you what...I too am based in Australia (Sydney).  I'm happy to provide consulting services to help you solve your problem.  Or, I can provide individual training if you want a more long term, improve your skills, "teach a man to fish" approach (if you're in Sydney).

My rate is $100AUD/hour.  Minimum of 2 hours paid in advance.  Let me know if you want my PayPal details.

I'm unsubscribing from this thread.  Good luck.

Edit:  And you're also much less likely to continue to get assistance when you mark your post "Solved", then continue to post new requirements to the thread.  I suggest you unmark this thread as "Solved" so others will know you still need help.  Common sense, really.

[BaalaRaaji]

Hi @ScottBass 

Am really sorry to bother you or made you angry.

Please you don't need to worry about my skills...I was lil stressful at work..

I was able to get the output ..

below code for reference..

data TESTING(drop=Last_date Prev_date END_OF_MONTH);

set Base_data_bvsap_poss;

by coll_idnn_hk;

set Base_data_bvsap_poss( firstobs = 2 keep = act_alloc rename = (act_alloc = Last_date) )

Base_data_bvsap_poss( obs = 1 drop = _all_ );

Prev_date = ifn( first.coll_idnn_hk, (.), lag(act_alloc) );

Last_date = ifn( last.coll_idnn_hk, (.), Last_date );

Efft_date=Min(act_alloc);

Expiry_date=Last_date ;

/*if Expiry_date= . then 31DEC9999';*/

format Prev_date Last_date Efft_date Expiry_date date9.;

run;

Please you can ignore my queries going forward..thanks

Cheers

[novinosrin]

Hello @BaalaRaaji   The SAS communities are very happy to help but please do not post questions after questions (as in your case "I am stuck with other logic"). within one thread. Your intitial question alone should be the focus and of course you can have follow up questions as a review of the initial question. This is how it works. 

So in a nutshell

Your initial post should have

1. A sample data of what you HAVE (paste here as text for us to copy paste and work with it)

2. A sample data of what you WANT(your expected output for the input sample)

3. A brief explanation of the convert/business logic and that is your objective

Keep the question clear for reading ease if you can with data and business logic as mentioned in points 1,2 and 3

Then the thread should keep up to that question as the main subject and not have multiple other questions. I hope this makes sense. So basically you asked for help, it was answered and then the thread was closed and then you move on to the next thread with a new subject.

You are welcome to open up any number of new threads with distinct questions and we the community are more than happy to help. We indeed want to provide the best friendly help as much as possible to all users around the globe and to do that we would want everyone to follow certain set standard practices. Take care.

Best Regards!

[BaalaRaaji]

Sure.. thank you @novinosrin 

The second query was based on the same data so I have posted here.. anyways thank you..

Much appreciated for the help.

cheers