BookmarkSubscribeRSS Feed
preston
Calcite | Level 5

I have multiple different data sets, and I am trying to use two at a time for calculations. For instance:

I have a dataset with approximately 90,000 rows, each row has columns: policy , age, gender, loyalty (years with company), smoker, zip code, accidents, premium.

I have another dataset with 6 rows. each row has columns: accidents, Charge.

What I am trying to do is take the accidents from the first dataset for each row and reference that number of accidents with the same number in the second dataset under the accidents column, and then use what is in the charge column for that many accidents (by row) as a multiplier. 

I have spent a few hours trying different things and haven't really come up with any solutions.

 

EDIT: I have uploaded a visual I quickly made to make it easier to understand what I am trying to accomplish


visual.png
6 REPLIES 6
Reeza
Super User
This is a lookup, there are quite a few posts on here about the most efficient way. I suggest looking into proc format and a left join.

Here's a paper that lists several other options:
http://www2.sas.com/proceedings/forum2008/095-2008.pdf
preston
Calcite | Level 5
I will look into that and read that link asap. thanks
preston
Calcite | Level 5

I'm not having any luck with that info. All I can figure out how to do with a lookup is take an individual policy and perform my desired actions, I need to create a new table with every value from one dataset multiplied by its corresponding value from the other dataset

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

proc sql;
  create table WANT as
  select  A.*,
          B.CHARGE,
          A.ANNUAL_PREMIUM * CHARGE as NEW_PREMIUM
  from    WORK.HAVE A
  left join WORK.ACCIDENTS
  on      A.ACCIDENTS=B.ACCIDENTS;
quit;
preston
Calcite | Level 5

Thanks, I'm going to bed now But I will work with this in the morning!

Steelers_In_DC
Barite | Level 11

data one;
input policy gender$ age loyalty smoker$ zipcode accidents annual_premium;
cards;
2 M 71 1 N 10468 0 1371.05
3 F 63 2 M 10500 1 865.34
;
run;

data two;
input accidents charge;
cards;
0 1
1 1.04
;
run;

data want;
merge one (in=a)
      two (in=b);
by accidents;
if a and b;
new_premium = annual_premium*charge;
run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 1083 views
  • 0 likes
  • 4 in conversation