topic Re: Find threshold value in SAS Programming

Find threshold value

JeffM1968 — Fri, 01 May 2020 18:01:25 GMT

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;

Re: Find threshold value

PaigeMiller — Fri, 01 May 2020 18:10:40 GMT

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.

Re: Find threshold value

Reeza — Fri, 01 May 2020 18:14:25 GMT

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?

Re: Find threshold value

JeffM1968 — Fri, 01 May 2020 18:34:07 GMT

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

Re: Find threshold value

FreelanceReinh — Fri, 01 May 2020 19:10:15 GMT

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.