How can I create multiple branches in SAS?

[PrudhviB]

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.

[s_lassen]

Please show your example data as data step code and not in excel format.

[Kurt_Bremser]

Please post example data in data steps with datalines, as you have been shown multiple times.

[Tom · Accepted solution]

Don't share data as spreadsheets.  It is trivial to write a data step instead and much more descriptive and portable.

Spoiler

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        *
...