topic Re: SAS Tricky Query in SAS Programming

SAS Tricky Query

BaalaRaaji — Thu, 18 Apr 2019 05:53:28 GMT

Hi Team,

I am stuck in the SAS QUery to filter the data.

The below example is for one account but my orginal data got 1million accounts.

Base data is the account level from 31jan2010 to 30APR2019

Sample data for one account.

COLL_IDNN_HK	MAT_CLREF	END_OF_MONTH	ACT_CODE
2CD3B	101	31Jan2010
2CD3B	101	28Feb2010
2CD3B	101	31Mar2010
2CD3B	101	30Apr2010
2CD3B	101	31May2010
2CD3B	101	30Jun2010
2CD3B	101	31Jul2010
2CD3B	101	31Aug2010
2CD3B	101	30Sep2010
2CD3B	101	31Oct2010
2CD3B	101	30Nov2010
2CD3B	101	31Dec2010
2CD3B	101	31Jan2011
2CD3B	101	28Feb2011
2CD3B	101	31Mar2011
2CD3B	101	30Apr2011
2CD3B	101	31May2011
2CD3B	101	30Jun2011
2CD3B	101	31Jul2011
2CD3B	101	31Aug2011
2CD3B	101	30Sep2011
2CD3B	101	31Oct2011
2CD3B	101	30Nov2011
2CD3B	101	31Dec2011
2CD3B	101	31Jan2012
2CD3B	101	29Feb2012
2CD3B	101	31Mar2012
2CD3B	101	30Apr2012
2CD3B	101	31May2012
2CD3B	101	30Jun2012
2CD3B	101	31Jul2012
2CD3B	101	31Aug2012
2CD3B	101	30Sep2012
2CD3B	101	31Oct2012
2CD3B	101	30Nov2012
2CD3B	101	31Dec2012
2CD3B	101	31Jan2013
2CD3B	101	28Feb2013
2CD3B	101	31Mar2013
2CD3B	101	30Apr2013
2CD3B	101	31May2013
2CD3B	101	30Jun2013
2CD3B	101	31Jul2013
2CD3B	101	31Aug2013
2CD3B	101	30Sep2013
2CD3B	101	31Oct2013
2CD3B	101	30Nov2013
2CD3B	101	31Dec2013
2CD3B	101	31Jan2014
2CD3B	101	28Feb2014
2CD3B	101	31Mar2014
2CD3B	101	30Apr2014
2CD3B	101	31May2014
2CD3B	101	30Jun2014
2CD3B	101	31Jul2014
2CD3B	101	31Aug2014
2CD3B	101	30Sep2014
2CD3B	101	31Oct2014
2CD3B	101	30Nov2014
2CD3B	101	31Dec2014
2CD3B	101	31Jan2015
2CD3B	101	28Feb2015
2CD3B	101	31Mar2015
2CD3B	101	30Apr2015
2CD3B	101	31May2015
2CD3B	101	30Jun2015
2CD3B	101	31Jul2015
2CD3B	101	31Aug2015
2CD3B	101	30Sep2015
2CD3B	101	31Oct2015
2CD3B	101	30Nov2015
2CD3B	101	31Dec2015
2CD3B	101	31Jan2016
2CD3B	101	29Feb2016
2CD3B	101	31Mar2016
2CD3B	101	30Apr2016
2CD3B	101	31May2016
2CD3B	101	30Jun2016
2CD3B	101	31Jul2016
2CD3B	101	31Aug2016
2CD3B	101	30Sep2016
2CD3B	101	31Oct2016
2CD3B	101	30Nov2016
2CD3B	101	31Dec2016
2CD3B	101	31Jan2017
2CD3B	101	28Feb2017
2CD3B	101	31Mar2017
2CD3B	101	31Mar2017	psale
2CD3B	101	30Apr2017
2CD3B	101	31May2017
2CD3B	101	30Jun2017
2CD3B	101	31Jul2017
2CD3B	101	31Aug2017
2CD3B	101	31Aug2017	sumap
2CD3B	101	30Sep2017
2CD3B	101	31Oct2017
2CD3B	101	31Oct2017	sumu
2CD3B	101	30Nov2017	sumsv
2CD3B	101	30Nov2017
2CD3B	101	31Dec2017
2CD3B	101	31Jan2018
2CD3B	101	28Feb2018
2CD3B	101	28Feb2018	judct
2CD3B	101	31Mar2018	judo
2CD3B	101	31Mar2018
2CD3B	101	30Apr2018
2CD3B	101	31May2018
2CD3B	101	31May2018	ntva
2CD3B	101	30Jun2018	vlreq
2CD3B	101	30Jun2018	poss
2CD3B	101	30Jun2018	biarr
2CD3B	101	30Jun2018
2CD3B	101	31Jul2018
2CD3B	101	31Jul2018	vrec
2CD3B	101	31Aug2018	propr
2CD3B	101	31Aug2018	nsold
2CD3B	101	31Aug2018
2CD3B	101	30Sep2018
2CD3B	101	30Sep2018	soldc
2CD3B	101	30Sep2018	soldp
2CD3B	101	31Oct2018
2CD3B	101	30Nov2018
2CD3B	101	30Nov2018	post
2CD3B	101	30Nov2018	msset
2CD3B	101	30Nov2018	woffa
2CD3B	101	30Nov2018	fin
2CD3B	101	31Dec2018
2CD3B	101	31Jan2019
2CD3B	101	28Feb2019
2CD3B	101	31Mar2019
2CD3B	101	30Apr2019

The logic I wanted to write is

First Step:

Delete the rows below oldest act_code and

Delete the rows above newest act_code

Need to see like below data...

COLL_IDNN_HK	MAT_CLREF	END_OF_MONTH	ACT_CODE
2CD3B	101	31Mar2017	psale
2CD3B	101	30Apr2017
2CD3B	101	31May2017
2CD3B	101	30Jun2017
2CD3B	101	31Jul2017
2CD3B	101	31Aug2017
2CD3B	101	31Aug2017	sumap
2CD3B	101	30Sep2017
2CD3B	101	31Oct2017
2CD3B	101	31Oct2017	sumu
2CD3B	101	30Nov2017	sumsv
2CD3B	101	30Nov2017
2CD3B	101	31Dec2017
2CD3B	101	31Jan2018
2CD3B	101	28Feb2018
2CD3B	101	28Feb2018	judct
2CD3B	101	31Mar2018	judo
2CD3B	101	31Mar2018
2CD3B	101	30Apr2018
2CD3B	101	31May2018
2CD3B	101	31May2018	ntva
2CD3B	101	30Jun2018	vlreq
2CD3B	101	30Jun2018	poss
2CD3B	101	30Jun2018	biarr
2CD3B	101	30Jun2018
2CD3B	101	31Jul2018
2CD3B	101	31Jul2018	vrec
2CD3B	101	31Aug2018	propr
2CD3B	101	31Aug2018	nsold
2CD3B	101	31Aug2018
2CD3B	101	30Sep2018
2CD3B	101	30Sep2018	soldc
2CD3B	101	30Sep2018	soldp
2CD3B	101	31Oct2018
2CD3B	101	30Nov2018
2CD3B	101	30Nov2018	post
2CD3B	101	30Nov2018	msset
2CD3B	101	30Nov2018	woffa
2CD3B	101	30Nov2018	fin

Secong Step:

Populate the missing within the value from previous month for the action code.

Final dataset should be like this...

COLL_IDNN_HK	MAT_CLREF	END_OF_MONTH	ACT_CODE
2CD3B	101	31Mar2017	psale
2CD3B	101	30Apr2017	psale
2CD3B	101	31May2017	psale
2CD3B	101	30Jun2017	psale
2CD3B	101	31Jul2017	psale
2CD3B	101	31Aug2017	psale
2CD3B	101	31Aug2017	sumap
2CD3B	101	30Sep2017	sumap
2CD3B	101	31Oct2017	sumap
2CD3B	101	31Oct2017	sumu
2CD3B	101	30Nov2017	sumsv
2CD3B	101	30Nov2017	sumsv
2CD3B	101	31Dec2017	sumsv
2CD3B	101	31Jan2018	sumsv
2CD3B	101	28Feb2018	sumsv
2CD3B	101	28Feb2018	judct
2CD3B	101	31Mar2018	judo
2CD3B	101	31Mar2018	judo
2CD3B	101	30Apr2018	judo
2CD3B	101	31May2018	judo
2CD3B	101	31May2018	ntva
2CD3B	101	30Jun2018	vlreq
2CD3B	101	30Jun2018	poss
2CD3B	101	30Jun2018	biarr
2CD3B	101	30Jun2018	biarr
2CD3B	101	31Jul2018	biarr
2CD3B	101	31Jul2018	vrec
2CD3B	101	31Aug2018	propr
2CD3B	101	31Aug2018	nsold
2CD3B	101	31Aug2018	nsold
2CD3B	101	30Sep2018	nsold
2CD3B	101	30Sep2018	soldc
2CD3B	101	30Sep2018	soldp
2CD3B	101	31Oct2018	soldp
2CD3B	101	30Nov2018	soldp
2CD3B	101	30Nov2018	post
2CD3B	101	30Nov2018	msset
2CD3B	101	30Nov2018	woffa
2CD3B	101	30Nov2018	fin

Please let me know if its not clear...thank you for helping out

Regards,

Re: SAS Tricky Query

ScottBass — Thu, 18 Apr 2019 06:56:03 GMT

See https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

and repost with a self-contained "have" and "want" data steps. Format your code using the "Insert SAS Code" icon.

Step 1: Use a surrogate key (incrementing number), pre-process your data getting the min and max SK with non-missing act_code grouped by COLL_IDNN_HK, then delete rows not between min and max sk.

Step 2: See https://communities.sas.com/t5/SAS-Programming/LOCF/m-p/395676/highlight/true#M95474 for LOCF examples.

I could have provided code instead of a word description if you'd bothered with the first step.

Re: SAS Tricky Query

andreas_lds — Thu, 18 Apr 2019 06:52:15 GMT

The second step can be solve by retaining an additional variable that is set to act_code and will populate act_code if it is missing. To provide something useful for the first step, i need data in usable form: a data step with datalines-statement. Maybe a retained variable can help here, too.

Re: SAS Tricky Query

novinosrin — Thu, 18 Apr 2019 13:39:22 GMT

Hi @BaalaRaaji It's pretty straight forward. A sound knowledge of by group processing and joins for look up will help.


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	
;


proc sql;
create table temp(drop=t) as
select COLL_IDNN_HK,MAT_CLREF,ACT_CODE>' ' as t,min(END_OF_MONTH) as _min format=date9., max(END_OF_MONTH) as _max format=date9.
from have
group by COLL_IDNN_HK,MAT_CLREF, t
having t;
quit;

data want;
merge have temp;
by COLL_IDNN_HK MAT_CLREF;
if (act_code>' ' and END_OF_MONTH=_min)  or (act_code>' ' and END_OF_MONTH=_max) or
_min<END_OF_MONTH<_max;
retain _mm;
length _mm $10;
if first.MAT_CLREF then call missing(_mm);
if not missing(act_code) then _mm=act_code;
else act_code=_mm;
drop _:;
run;

Re: SAS Tricky Query

Shmuel — Thu, 18 Apr 2019 14:18:40 GMT

Assuming data set is sorted by Coll_IDNN_HK MAT_CLREF END_OF_MONTH , then

you can achieve what you want by few steps:

data temp1;
  set have;
       retain flag 0;
       if flag=0 and missing(act_code) then delete;
       else flag=1;
run;

proc sort data=temp1;
  by Coll_IDNN_HK  MAT_CLREF descending END_OF_MONTH;
run;

data temp2;
 set temp1;
       retain flag 0;
       if flag=0 and missing(act_code) then delete;
       else flag=1;
run;

proc sort data=temp2;
  by Coll_IDNN_HK  MAT_CLREF END_OF_MONTH;
run;

data want;
 set temp2;
       retain prev_code;
       if not missing(act_code)
       then prev_code = act_code;
       else act_code = prev_code;
run;

Re: SAS Tricky Query

BaalaRaaji — Tue, 23 Apr 2019 01:26:38 GMT

Hi novinosrin,

Super User...Thank you so much for the code. Much Appreciated..

Brilliant thinking in replicated the code..Awesome.

You are a SAS Expert.thanks again

Cheers!

Re: SAS Tricky Query

BaalaRaaji — Tue, 23 Apr 2019 01:28:16 GMT

Thanks Shmuel..Much Appreciated...

Re: SAS Tricky Query

BaalaRaaji — Mon, 29 Apr 2019 01:55:35 GMT

The code is working properly but when I have added new variable(act_alloc_date)..its not working..?

I have added this feild because in a month I have 4 different act_code and need the last one to order..

would you be able to advise? appreciate your help.thanks

Re: SAS Tricky Query

BaalaRaaji — Mon, 29 Apr 2019 02:02:52 GMT

Below code ...some how not working if I add act_alloc(date)

/* Base data */

proc sql;

create table test as select a.coll_idnn_hk,a.MAT_CLREF ,a.END_OF_MONTH,b.ACT_CODE,b.act_alloc

from HK_MONTH_DAT as a

left join com_HK as b on

a.coll_idnn_hk=b.coll_idnn_hk and a.END_OF_MONTH= b.END_OF_MONTH

where MAT_CLREF ne ' '

order by a.COLL_IDNN_HK,a.MAT_CLREF,b.act_alloc;

quit;

/**/

/*proc sort data=test out= x1;by coll_idnn_hk MAT_CLREF act_alloc ;run; */

proc sql;

create table temp(drop=t) as

select COLL_IDNN_HK,MAT_CLREF,ACT_CODE>' ' as t,min(END_OF_MONTH) as _min format=date9., max(END_OF_MONTH) as _max format=date9.

from test

group by COLL_IDNN_HK,MAT_CLREF,t

having t;

quit;

data want;

merge test temp;

by COLL_IDNN_HK MAT_CLREF ;

/*if (act_code>' ' and END_OF_MONTH=_min) or (act_code>' ' and END_OF_MONTH=_max) or*/

/*_min<END_OF_MONTH<_max;*/

retain _mm;

length _mm $10;

if first.MAT_CLREF then call missing(_mm);

if not missing(act_code) then _mm=act_code;

else act_code=_mm;

drop _:;

run;

proc sort data=want1 out= c1; by coll_idnn_hk end_of_month act_alloc act_code ;run;

Re: SAS Tricky Query

novinosrin — Mon, 29 Apr 2019 02:18:07 GMT

Hi @BaalaRaaji Can you hang in there for a night as it is 9:20pm chicago time. Let me test in the morning and message you back.

Re: SAS Tricky Query

BaalaRaaji — Mon, 29 Apr 2019 02:19:25 GMT

sorry, Ignore it..i have fixed it..

your code is still working..thanks a lot..Appreciate it..

Chees

Re: SAS Tricky Query

BaalaRaaji — Mon, 29 Apr 2019 02:29:05 GMT

Hey, Sorry to bother and distribut you..Am from Australia and was working to finialise this code..

please don't worry as the code is working perfectly..

Thanks and Reagrds,

Re: SAS Tricky Query

novinosrin — Mon, 29 Apr 2019 02:38:38 GMT

Always happy to help and feel free. Just that I do not have access to SAS software at home and so the reason in not being able to help anybody. So in future, weekends at nights are a diffiicult as my typical routine is friends, restaurants and other chores, otherwise SAS is always fun.

Nonetheless i'm always online here on SAS communities on my mobile phone

Re: SAS Tricky Query

BaalaRaaji — Mon, 29 Apr 2019 06:27:49 GMT

Appreciate your help. Definitely will point you directly if I stuck with the logics.

thank you

Re: SAS Tricky Query

ScottBass — Mon, 29 Apr 2019 21:23:39 GMT

Here is the approach I was suggesting. I get a slightly different result than @novinosrin (obs 35, soldp).

Assuming my business logic is correct, I find my code a bit easier to follow (but of course everyone would think their own code is easier to follow - that's just natural). The big question is which code is creating the correct result?

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
;
run;

* my approach ;
data sk / view=sk;
   sk+1;
   set have;
run;

proc sql;
   create table min_max as
   select COLL_IDNN_HK, min(sk) as min_sk, max(sk) as max_sk
   from sk
   where ACT_CODE is not missing
   group by COLL_IDNN_HK;

   create table want as
   select a.*
   from sk a
   inner join min_max b
   on a.COLL_IDNN_HK=b.COLL_IDNN_HK
   where a.sk between min_sk and max_sk;
quit;

* last observation carried forward (LOCF) ;
data want;
   update want (obs=0) want;
   by COLL_IDNN_HK;
   output;
   drop sk;
run;

* @novinosrin's approach ;
proc sql;
create table temp(drop=t) as
select COLL_IDNN_HK,MAT_CLREF,ACT_CODE>' ' as t,min(END_OF_MONTH) as _min format=date9., max(END_OF_MONTH) as _max format=date9.
from have
group by COLL_IDNN_HK,MAT_CLREF, t
having t;
quit;

data want2;
merge have temp;
by COLL_IDNN_HK MAT_CLREF;
if (act_code>' ' and END_OF_MONTH=_min)  or (act_code>' ' and END_OF_MONTH=_max) or
_min<END_OF_MONTH<_max;
retain _mm;
length _mm $10;
if first.MAT_CLREF then call missing(_mm);
if not missing(act_code) then _mm=act_code;
else act_code=_mm;
drop _:;
run;

proc compare base=want2 comp=want;
run;

Re: SAS Tricky Query

novinosrin — Mon, 29 Apr 2019 14:16:01 GMT

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;

Re: SAS Tricky Query

BaalaRaaji — Tue, 30 Apr 2019 01:07:08 GMT

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

Much Appreciated..

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

Re: SAS Tricky Query

BaalaRaaji — Tue, 30 Apr 2019 01:48:05 GMT

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!

Re: SAS Tricky Query

BaalaRaaji — Tue, 30 Apr 2019 01:51:57 GMT

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!

Re: SAS Tricky Query

BaalaRaaji — Tue, 30 Apr 2019 03:41:27 GMT

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