Solved: SAS Tricky Query

BaalaRaaji · Posted 04-18-2019 01:53 AM

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,

RS

novinosrin · Posted 04-18-2019 09:39 AM

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;

View solution in original post

ScottBass · Posted 04-18-2019 02:50 AM

See https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

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.

Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

andreas_lds · Posted 04-18-2019 02:52 AM

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.

novinosrin · Posted 04-18-2019 09:39 AM

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;

BaalaRaaji · Posted 04-22-2019 09:26 PM

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!

BaalaRaaji · Posted 04-28-2019 09:55 PM

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

BaalaRaaji · Posted 04-28-2019 10:02 PM

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;

novinosrin · Posted 04-28-2019 10:18 PM

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.

BaalaRaaji · Posted 04-28-2019 10:29 PM

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,

RS

novinosrin · Posted 04-28-2019 10:34 PM

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.

PS

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

BaalaRaaji · Posted 04-29-2019 02:27 AM

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

thank you

BaalaRaaji · Posted 04-28-2019 10:19 PM

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

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

Chees

RS

Shmuel · Posted 04-18-2019 10:18 AM

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;

BaalaRaaji · Posted 04-22-2019 09:28 PM

Thanks Shmuel..Much Appreciated...

ScottBass · Posted 04-29-2019 04:43 AM

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;

Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.

SAS Tricky Query

Re: SAS Tricky Query

Re: SAS Tricky Query

Re: SAS Tricky Query

Re: SAS Tricky Query

Re: SAS Tricky Query

Re: SAS Tricky Query

Re: SAS Tricky Query

Re: SAS Tricky Query

Re: SAS Tricky Query

Re: SAS Tricky Query

Re: SAS Tricky Query

Re: SAS Tricky Query

Re: SAS Tricky Query

Re: SAS Tricky Query

Re: SAS Tricky Query

Classroom Training Available!