Fluorite | Level 6

## Find threshold value

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;``````
1 ACCEPTED SOLUTION

Accepted Solutions

## Re: Find threshold value

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.

4 REPLIES 4
Diamond | Level 26

## Re: Find threshold value

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.

--
Paige Miller
Super User

## Re: Find threshold value

``````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?

Fluorite | Level 6

## Re: Find threshold value

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

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.

Discussion stats
• 4 replies
• 1317 views
• 1 like
• 4 in conversation