BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
alepage
Barite | Level 11

How do we estimate the growth for written premium (wp) , Earned Unit (EU) and Written Unit (WU) from 2022 to 2023 with a dataset structured like below

 

DATA new_dataset;
INPUT AN WP EU WU;
DATALINES;
2022 1000 1500 1700
2023 1500 1550 1800
;
RUN;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User
data result;
   set new_dataset;
   by ctradm type3;
   growth_wp= dif(wp)/lag(wp);
   growth_eu= dif(eu)/lag(eu);
   growth_wu= dif(wu)/lag(wu);
   if last.type3;
run;

For your given data.

 

What this does:

The DIF function calculates the difference for a variable from the previous observation, lag gets the value of the variable for the previous observation. You can actual get the value from more than the previous: DIF4 gets the difference between the current and the 4th previous observation.

Caveat: Previous is within a queued value group. So when used with "if" or similar conditions the "previous" is the previous time the condition is true.

 

This will result in notes in the log about missing values because on the first observation is there no "previous" value to use.

 

The BY statement creates automatic variables that indicate to SAS whether the current observation is the first or last of a by group. The values are accessed with the syntax FIRST.VARIABLE or LAST.VARIABLE and are valued as 1 for true and 0 for false. So IF statements like 'if first.variable then ...' execute ;

The IF used above is a subsetting IF so that only the observation with the last of the TYPE3 is written to the output.

 

Caution: Your example data implies a sort order by Type3. If this is not the actual case but the data is grouped as need then add NOTSORTED to the BY statement to prevent errors.

Or sort the data by CTRADM TYPE3 AN prior to this step.

View solution in original post

4 REPLIES 4
ballardw
Super User

What would the result look like? And what if the value does not increase?

If you have multiple years do you want a year-to-year change or over the time period of record?

 

Does your actual data have something else, perhaps an identifier as to what product or client name that groups records together (goes to a better fit of a solution to actual problem)?

Reeza
Super User
With just year over year change you can't quite make predictions in my opinion, in this case you get more into the art method of predictions than science.
alepage
Barite | Level 11

Sorry, may be I was not clear enough.  I just want to estimate the variation of three variable from the previous year to the current year, like below.  You may have many administrative center and type3 class but the dataset structure will be like below.  The result dataset is the one I would like to have.  What's the best way to do that ?

 

DATA new_dataset;
INPUT AN CTRADM $ TYPE3 WP EU WU;
DATALINES;
2022 ABL 23 1000 1500 1700
2023 ABL 23 1500 1550 1800
2022 ABL 31.1 800 800 800
2023 ABL 31.1 1000 950 900

;
RUN;

DATA Result;
INPUT AN CTRADM $ TYPE3 WP growth_WP EU growth_EU WU growth_WU;
DATALINES;

2023 ABL 23 1500 0.5 1550 0.033 1800 0.058
2023 ABL 31.1 1000 0.25 950 0.1875 900 0.125
;
RUN;
ballardw
Super User
data result;
   set new_dataset;
   by ctradm type3;
   growth_wp= dif(wp)/lag(wp);
   growth_eu= dif(eu)/lag(eu);
   growth_wu= dif(wu)/lag(wu);
   if last.type3;
run;

For your given data.

 

What this does:

The DIF function calculates the difference for a variable from the previous observation, lag gets the value of the variable for the previous observation. You can actual get the value from more than the previous: DIF4 gets the difference between the current and the 4th previous observation.

Caveat: Previous is within a queued value group. So when used with "if" or similar conditions the "previous" is the previous time the condition is true.

 

This will result in notes in the log about missing values because on the first observation is there no "previous" value to use.

 

The BY statement creates automatic variables that indicate to SAS whether the current observation is the first or last of a by group. The values are accessed with the syntax FIRST.VARIABLE or LAST.VARIABLE and are valued as 1 for true and 0 for false. So IF statements like 'if first.variable then ...' execute ;

The IF used above is a subsetting IF so that only the observation with the last of the TYPE3 is written to the output.

 

Caution: Your example data implies a sort order by Type3. If this is not the actual case but the data is grouped as need then add NOTSORTED to the BY statement to prevent errors.

Or sort the data by CTRADM TYPE3 AN prior to this step.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 509 views
  • 1 like
  • 3 in conversation