Hi SAS Experts,
I am trying to multiple each record in my Have data set to derive Want data set (as show in the attached excel file).
I have date set shown under Have sheet by multiple cutoff fields and want to introduce a new cutoff (under Cutoff - Columne "F") which is based on Type(col "B"),
ideally this will multiple my each row into respective Cutoffs based on type.(see sheet "Cutoff based on type")
i created want data set for first 10 rows. with a new column New_policy_id which will be incremented or decreased depending on the cutoff value(col "F").
in a nut shell, for single record i would like to have new rows depending on the cutoffs + parent row(which is the original row) as shown in the want data.
I have tried different approaches and didnt seem to find a solutions, this looks so complicated, any help on this is huge thing for me.
Don't share data as spreadsheets. It is trivial to write a data step instead and much more descriptive and portable.
data cutoff;
row+1;
input type :$1. Cutoff $20.;
if type ne lag(type) then row=1;
cards;
a Parent
a <200
a >=200
b Parent
b <140
b 140 - 160
b >=160
c Parent
c <180
c >=180
d Parent
d <200
d >=200
e Parent
e <140
e 140 - 180
e >=180
;
data have ;
input Record_key (type Region Reading_1 Reading_2 Cutoff Utilization Threshold tenure) ($)
Policy_id
;
cards;
1 a <25 * * * * * * 1
2 a 25-50 <=500 * * * * <=2 2
3 a 25-50 <=500 * * * * >2 3
4 a >50 >850 * * * * * 4
5 a >50 <=657 * * * * * 5
6 b <25 * * * * * * 6
7 b 25-50 <=500 * * <=8 * * 7
8 b 25-50 >500 * * >8 * * 8
9 b >50 <=657 <=850 * * * * 9
10 b >50 <=657 >850 * * * * 10
11 b <25 <=714 * * * * * 11
12 b 25-50 <=500 * * * * * 12
13 c 25-50 >500 * * * * * 13
14 c >50 >850 * * * * * 14
15 c >50 <=657 * * * * * 15
16 c <25 * * * * * * 16
17 e 25-50 <=500 * * <=58 * * 17
18 e 25-50 <=500 * * >58 * * 18
19 e >50 >850 * * * * * 19
20 e >50 <=657 * * * * * 20
21 e <25 * * * * * * 21
22 e 25-50 <=500 <=946 * * * * 22
23 e 25-50 <=500 >946 * * * * 23
24 d >50 >850 * * * * * 24
25 d >50 <=657 * * * * * 25
26 d <25 * * * * * * 26
27 d 25-50 <=500 * * * <=13.5 * 27
28 d 25-50 <=500 * * * >13.5 * 28
29 d >50 >850 * * * * * 29
30 d >50 <=657 * * * * * 30
;
Looks to me like you just want to join the two tables.
proc sql ;
create table want as
select *
from cutoff natural left join have(drop=Cutoff)
order by policy_id, row
;
quit;
proc print;
var policy_id row cutoff Region Reading_1 Reading_2;
run;
Policy_ Reading_ Reading_ OBS id row Cutoff Region 1 2 1 1 1 Parent <25 * * 2 1 2 <200 <25 * * 3 1 3 >=200 <25 * * 4 2 1 Parent 25-50 <=500 * 5 2 2 <200 25-50 <=500 * 6 2 3 >=200 25-50 <=500 * 7 3 1 Parent 25-50 <=500 * 8 3 2 <200 25-50 <=500 * 9 3 3 >=200 25-50 <=500 * 10 4 1 Parent >50 >850 * 11 4 2 <200 >50 >850 * 12 4 3 >=200 >50 >850 * 13 5 1 Parent >50 <=657 * 14 5 2 <200 >50 <=657 * 15 5 3 >=200 >50 <=657 * 16 6 1 Parent <25 * * 17 6 2 <140 <25 * * 18 6 3 140 - 160 <25 * * 19 6 4 >=160 <25 * * 20 7 1 Parent 25-50 <=500 * 21 7 2 <140 25-50 <=500 * 22 7 3 140 - 160 25-50 <=500 * 23 7 4 >=160 25-50 <=500 * 24 8 1 Parent 25-50 >500 * 25 8 2 <140 25-50 >500 * ...
Please show your example data as data step code and not in excel format.
Please post example data in data steps with datalines, as you have been shown multiple times.
Don't share data as spreadsheets. It is trivial to write a data step instead and much more descriptive and portable.
data cutoff;
row+1;
input type :$1. Cutoff $20.;
if type ne lag(type) then row=1;
cards;
a Parent
a <200
a >=200
b Parent
b <140
b 140 - 160
b >=160
c Parent
c <180
c >=180
d Parent
d <200
d >=200
e Parent
e <140
e 140 - 180
e >=180
;
data have ;
input Record_key (type Region Reading_1 Reading_2 Cutoff Utilization Threshold tenure) ($)
Policy_id
;
cards;
1 a <25 * * * * * * 1
2 a 25-50 <=500 * * * * <=2 2
3 a 25-50 <=500 * * * * >2 3
4 a >50 >850 * * * * * 4
5 a >50 <=657 * * * * * 5
6 b <25 * * * * * * 6
7 b 25-50 <=500 * * <=8 * * 7
8 b 25-50 >500 * * >8 * * 8
9 b >50 <=657 <=850 * * * * 9
10 b >50 <=657 >850 * * * * 10
11 b <25 <=714 * * * * * 11
12 b 25-50 <=500 * * * * * 12
13 c 25-50 >500 * * * * * 13
14 c >50 >850 * * * * * 14
15 c >50 <=657 * * * * * 15
16 c <25 * * * * * * 16
17 e 25-50 <=500 * * <=58 * * 17
18 e 25-50 <=500 * * >58 * * 18
19 e >50 >850 * * * * * 19
20 e >50 <=657 * * * * * 20
21 e <25 * * * * * * 21
22 e 25-50 <=500 <=946 * * * * 22
23 e 25-50 <=500 >946 * * * * 23
24 d >50 >850 * * * * * 24
25 d >50 <=657 * * * * * 25
26 d <25 * * * * * * 26
27 d 25-50 <=500 * * * <=13.5 * 27
28 d 25-50 <=500 * * * >13.5 * 28
29 d >50 >850 * * * * * 29
30 d >50 <=657 * * * * * 30
;
Looks to me like you just want to join the two tables.
proc sql ;
create table want as
select *
from cutoff natural left join have(drop=Cutoff)
order by policy_id, row
;
quit;
proc print;
var policy_id row cutoff Region Reading_1 Reading_2;
run;
Policy_ Reading_ Reading_ OBS id row Cutoff Region 1 2 1 1 1 Parent <25 * * 2 1 2 <200 <25 * * 3 1 3 >=200 <25 * * 4 2 1 Parent 25-50 <=500 * 5 2 2 <200 25-50 <=500 * 6 2 3 >=200 25-50 <=500 * 7 3 1 Parent 25-50 <=500 * 8 3 2 <200 25-50 <=500 * 9 3 3 >=200 25-50 <=500 * 10 4 1 Parent >50 >850 * 11 4 2 <200 >50 >850 * 12 4 3 >=200 >50 >850 * 13 5 1 Parent >50 <=657 * 14 5 2 <200 >50 <=657 * 15 5 3 >=200 >50 <=657 * 16 6 1 Parent <25 * * 17 6 2 <140 <25 * * 18 6 3 140 - 160 <25 * * 19 6 4 >=160 <25 * * 20 7 1 Parent 25-50 <=500 * 21 7 2 <140 25-50 <=500 * 22 7 3 140 - 160 25-50 <=500 * 23 7 4 >=160 25-50 <=500 * 24 8 1 Parent 25-50 >500 * 25 8 2 <140 25-50 >500 * ...
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.