Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Using data from two datasets for an equation.

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 12-01-2015 11:41 PM
(1500 views)

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

6 REPLIES 6

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

Here's a paper that lists several other options:

http://www2.sas.com/proceedings/forum2008/095-2008.pdf

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

I will look into that and read that link asap. thanks

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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 2025** is scheduled for May 6-9 in Orlando, FL. Sign up to be **first to learn** about the agenda and registration!

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.

Ready to level-up your skills? Choose your own adventure.