BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
JeffM1968
Fluorite | Level 6

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
FreelanceReinh
Jade | Level 19

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.

 

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26

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
Reeza
Super User
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
Fluorite | Level 6

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
Jade | Level 19

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.

 

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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