Find threshold value

[JeffM1968]

Howdy,

I am running some code that will sort products by price, tally a cumulative percent of units, and then create quartiles based on the cumulative percentage.

However, what I also want to do is create a new price variable that "caps" the price because there are some really whacky high prices that I do not want to come through.  What I am trying to do is find when the cumulative unit percentage crosses 98% and then only use that price going forward.

Here is my code.  The bottom part is the incorrect part.  I know i'm using the lag function improperly.  Any help is greatly appreciated!

Jeff

ata work.asp_range_03;
	set work.asp_range_02;
	by ProdLvl_1 ProdLvl_2 ProdLvl_3 ProdLvl_4 'asp ceil'n;
    
    /* Calculate running total of Units */ 
	if first.ProdLvl_4 then Cum_Units =0;
	Cum_Units + Units;

	/* Running % of total units */
	'% Units'n = Cum_Units/'Total Units'n;

	/* Identify quartiles.  Descending order prevents quartile skipping */
	if '% Units'n >=.75 then Quartile=4;
	else if '% Units'n >=.50 then Quartile=3;
	else if '% Units'n >=.25 then Quartile=2;
	else Quartile=1;

	/* Cap maximum ASP Ceil */
	ASP_New = 'ASP Ceil'n;
	if '% Units'n >=.98 then ASP_New = Lag(ASP_New);
run;

[PaigeMiller]

I haven't tried finding out what is wrong with your LAG function usage.

When you want to determine where 98% of the data lies, you should use PROC RANK with  the FRACTION option. This will enable you to see what data lies in the lower 98%. An alternative is PROC UNIVARIATE which will  give you the 98th percentile.

[Reeza]

ASP_New = 'ASP Ceil'n;
temp = lag(asp_new);
if '% Units'n >=.98 then ASP_New = temp;

You're aware that PROC RANK can calculate the ranks for you? And that you can use variable labels instead of variable names to avoid having to have variables such as '% Ceil'n?

[JeffM1968]

I am not that familiar with some of the PROC procedures.  I usually develop in EG and then convert to code.  I'll have to look into some of them!

Thanks

[FreelanceReinh · Accepted solution]

Hi @JeffM1968,

I think you can simply correct your existing code:

Either use 

if '% Units'n >=.98 then do;
  if _tmp=.m then _tmp=ASP_New;
  ASP_New=_tmp;
end;
retain _tmp .m;
drop _tmp;

if you want to carry forward the ASP_New value of the first observation with '% Units'n >=.98

or use

lag_ASP_New=lag(ASP_New);
if '% Units'n >=.98 then do;
  if _tmp=.m then _tmp=lag_ASP_New;
  ASP_New=_tmp;
end;
retain _tmp .m;
drop _tmp lag_ASP_New;

if you want to carry forward the ASP_New value of the observation before the first observation with '% Units'n >=.98.