Prev: What concerns me the most about Enterprise Guide
Next: online SmartPLS SEM workshops in February
From: Mike Zdeb on 11 Jan 2010 11:21
hi ... one approach ... use the cut points to produce
a format that's used later in another data step

this assumes that the variable ADD_MONTHS is always numbered
consecutively starting with the value for the first cut point


* the changing cut points;
data cut;
input cp @@;
datalines;
22 53 73 84 87 92 98 103 109
;
run;

* create a format;
data cut;
retain fmtname 'cpts';
set cut end=last;
start = sum(lag(cp),1);
if _n_ eq 1 then hlo = 'l';
else
if last then hlo = 'h';
end = cp;
label = put(_n_,best.);
run;

proc format cntlin=cut;
* take a look;
select cpts;
run;

* the data;
data junk;
input b @@;
datalines;
31 50 71 80 90 68 90 90 5 2
22 53 73 84 87 92 98 103 109
;
run;

* the data + add_months;
data new;
set junk;
* use the format;
add_months = input(put(b,cpts.),best.);
run;


--
Mike Zdeb
U(a)Albany School of Public Health
One University Place
Rensselaer, New York 12144-3456
P/518-402-6479 F/630-604-1475

> I have a data table with variable 'b'. I want to compare variable 'b'
> across a range of 9 values using a do loop. Depending on where
> varible 'b' falls I will create a new variable called add_months. I can
> program this manually but want to automate the process as the number or
> comparisons (levels) will change in future iterations. Below is a sample
> of the data and how I would achieve this manually.
>
> data junk;
> input b cutpt1 cutpt2 cutpt3 cutpt4 cutpt5 cutpt6 cutpt7 cutpt8
> cutpt9;
> datalines;
> 31 22 53 73 84 87 92 98
> 103 109
> 50 22 53 73 84 87 92 98 103 109
> 71 22 53 73 84 87 92 98 103 109
> 80 22 53 73 84 87 92 98 103 109
> 90 22 53 73 84 87 92 98 103 109
> 68 22 53 73 84 87 92 98 103 109
> 90 22 53 73 84 87 92 98 103 109
> 90 22 53 73 84 87 92 98 103 109
> 5 22 53 73 84 87 92 98 103 109
> 2 22 53 73 84 87 92 98 103 109
> ;
> run;
> data test;
> set junk;
> if b le 22 then add_months= 1;
> if b gt 22 and b le 53 then add_months=2;
> if b gt 53 and b le 73 then add_months=3;
> if b gt 73 and b le 84 then add_months=4;
> if b gt 84 and b le 87 then add_months=5;
> if b gt 87 and b le 92 then add_months=6;
> if b gt 92 and b le 98 then add_months=7;
> if b gt 98 and b le 103 then add_months=8;
> if b gt 103 then add_months=9;
> run;
>
From: Muthia Kachirayan on 11 Jan 2010 11:35
Ray,

Another way using array.


data need;
set junk;
array k[*] cutpt1 -- cutpt9;
if b le k[1] then add_months = 1;
else if b gt k[dim(k) - 1] then add_months = dim(k);
else
do _n_ = 2 to dim(k) - 1;
if k[_n_ - 1] LT b LE k[_n_] then add_months = _n_;
end;
run;

Muthia Kachirayan


On Mon, Jan 11, 2010 at 11:23 AM, Ray Harvey <rharvey3015(a)gmail.com> wrote:

> I have a data table with variable 'b'. I want to compare variable 'b'
> across a range of 9 values using a do loop. Depending on where
> varible 'b' falls I will create a new variable called add_months. I can
> program this manually but want to automate the process as the number or
> comparisons (levels) will change in future iterations. Below is a sample
> of the data and how I would achieve this manually.
>
> data junk;
> input b cutpt1 cutpt2 cutpt3 cutpt4 cutpt5 cutpt6 cutpt7 cutpt8
> cutpt9;
> datalines;
> 31 22 53 73 84 87 92 98
> 103 109
> 50 22 53 73 84 87 92 98 103 109
> 71 22 53 73 84 87 92 98 103 109
> 80 22 53 73 84 87 92 98 103 109
> 90 22 53 73 84 87 92 98 103 109
> 68 22 53 73 84 87 92 98 103 109
> 90 22 53 73 84 87 92 98 103 109
> 90 22 53 73 84 87 92 98 103 109
> 5 22 53 73 84 87 92 98 103 109
> 2 22 53 73 84 87 92 98 103 109
> ;
> run;
> data test;
> set junk;
> if b le 22 then add_months= 1;
> if b gt 22 and b le 53 then add_months=2;
> if b gt 53 and b le 73 then add_months=3;
> if b gt 73 and b le 84 then add_months=4;
> if b gt 84 and b le 87 then add_months=5;
> if b gt 87 and b le 92 then add_months=6;
> if b gt 92 and b le 98 then add_months=7;
> if b gt 98 and b le 103 then add_months=8;
> if b gt 103 then add_months=9;
> run;
>
From: Muthia Kachirayan on 11 Jan 2010 11:53
For efficiency, use

do _n_ = 2 to dim(k) - 1;
if k[_n_ - 1] LT b LE k[_n_] then do; add_months = _n_; leave; end;
end;

instead of

do _n_ = 2 to dim(k) - 1;
if k[_n_ - 1] LT b LE k[_n_] then add_months = _n_;
end;

On Mon, Jan 11, 2010 at 12:35 PM, Muthia Kachirayan <
muthia.kachirayan(a)gmail.com> wrote:

> Ray,
>
> Another way using array.
>
>
> data need;
> set junk;
> array k[*] cutpt1 -- cutpt9;
> if b le k[1] then add_months = 1;
> else if b gt k[dim(k) - 1] then add_months = dim(k);
> else
> do _n_ = 2 to dim(k) - 1;
> if k[_n_ - 1] LT b LE k[_n_] then add_months = _n_;
> end;
> run;
>
> Muthia Kachirayan
>
>
> On Mon, Jan 11, 2010 at 11:23 AM, Ray Harvey <rharvey3015(a)gmail.com>wrote:
>
>> I have a data table with variable 'b'. I want to compare variable 'b'
>> across a range of 9 values using a do loop. Depending on where
>> varible 'b' falls I will create a new variable called add_months. I can
>> program this manually but want to automate the process as the number or
>> comparisons (levels) will change in future iterations. Below is a sample
>> of the data and how I would achieve this manually.
>>
>> data junk;
>> input b cutpt1 cutpt2 cutpt3 cutpt4 cutpt5 cutpt6 cutpt7 cutpt8
>> cutpt9;
>> datalines;
>> 31 22 53 73 84 87 92 98
>> 103 109
>> 50 22 53 73 84 87 92 98 103 109
>> 71 22 53 73 84 87 92 98 103 109
>> 80 22 53 73 84 87 92 98 103 109
>> 90 22 53 73 84 87 92 98 103 109
>> 68 22 53 73 84 87 92 98 103 109
>> 90 22 53 73 84 87 92 98 103 109
>> 90 22 53 73 84 87 92 98 103 109
>> 5 22 53 73 84 87 92 98 103 109
>> 2 22 53 73 84 87 92 98 103 109
>> ;
>> run;
>> data test;
>> set junk;
>> if b le 22 then add_months= 1;
>> if b gt 22 and b le 53 then add_months=2;
>> if b gt 53 and b le 73 then add_months=3;
>> if b gt 73 and b le 84 then add_months=4;
>> if b gt 84 and b le 87 then add_months=5;
>> if b gt 87 and b le 92 then add_months=6;
>> if b gt 92 and b le 98 then add_months=7;
>> if b gt 98 and b le 103 then add_months=8;
>> if b gt 103 then add_months=9;
>> run;
>>
>
>
From: Francois van der Walt on 11 Jan 2010 17:20
Hi Ray,

I would love to know what the background to the need is.

One "pragmatic" solution will be to create a lookup table and then merge it
with your junk table. Another solution would be to use formats

Here is the lookup solution:

data junk;
input b cutpt1 cutpt2 cutpt3 cutpt4 cutpt5 cutpt6 cutpt7 cutpt8
cutpt9;
datalines;
31 22 53 73 84 87 92 98
103 109
50 22 53 73 84 87 92 98 103 109
71 22 53 73 84 87 92 98 103 109
80 22 53 73 84 87 92 98 103 109
90 22 53 73 84 87 92 98 103 109
68 22 53 73 84 87 92 98 103 109
90 22 53 73 84 87 92 98 103 109
90 22 53 73 84 87 92 98 103 109
5 22 53 73 84 87 92 98 103 109
2 22 53 73 84 87 92 98 103 109
;
run;
data test;
set junk;
if b le 22 then add_months= 1;
if b gt 22 and b le 53 then add_months=2;
if b gt 53 and b le 73 then add_months=3;
if b gt 73 and b le 84 then add_months=4;
if b gt 84 and b le 87 then add_months=5;
if b gt 87 and b le 92 then add_months=6;
if b gt 92 and b le 98 then add_months=7;
if b gt 98 and b le 103 then add_months=8;
if b gt 103 then add_months=9;
run;

Depending on your further need you can drop cutpt1 etc.

Kind Regards
Francois (Brisbane)

On Mon, 11 Jan 2010 10:23:03 -0500, Ray Harvey <rharvey3015(a)GMAIL.COM> wrote:

>I have a data table with variable 'b'. I want to compare variable 'b'
>across a range of 9 values using a do loop. Depending on where
>varible 'b' falls I will create a new variable called add_months. I can
>program this manually but want to automate the process as the number or
>comparisons (levels) will change in future iterations. Below is a sample
>of the data and how I would achieve this manually.
>
>data junk;
> input b cutpt1 cutpt2 cutpt3 cutpt4 cutpt5 cutpt6 cutpt7 cutpt8
>cutpt9;
> datalines;
> 31 22 53 73 84 87 92 98
> 103 109
>50 22 53 73 84 87 92 98 103 109
>71 22 53 73 84 87 92 98 103 109
>80 22 53 73 84 87 92 98 103 109
>90 22 53 73 84 87 92 98 103 109
>68 22 53 73 84 87 92 98 103 109
>90 22 53 73 84 87 92 98 103 109
>90 22 53 73 84 87 92 98 103 109
>5 22 53 73 84 87 92 98 103 109
>2 22 53 73 84 87 92 98 103 109
>;
>run;
>data test;
> set junk;
> if b le 22 then add_months= 1;
> if b gt 22 and b le 53 then add_months=2;
> if b gt 53 and b le 73 then add_months=3;
> if b gt 73 and b le 84 then add_months=4;
> if b gt 84 and b le 87 then add_months=5;
> if b gt 87 and b le 92 then add_months=6;
> if b gt 92 and b le 98 then add_months=7;
> if b gt 98 and b le 103 then add_months=8;
> if b gt 103 then add_months=9;
>run;
From: Francois van der Walt on 11 Jan 2010 17:25
Hi Ray,

I cut and paste the wrong solution!!! Here is the code again.

(While I worked on my solution you had 4 other replies which are all good!)

data lookup;
input b add_months;
datalines;
value Add-months
1 1
2 1
3 1
4 1
5 1
6 1
7 1
8 1
9 1
10 1
11 1
12 1
13 1
14 1
15 1
16 1
17 1
18 1
19 1
20 1
21 1
22 1
23 2
24 2
25 2
26 2
27 2
28 2
29 2
30 2
31 2
32 2
33 2
34 2
35 2
36 2
37 2
38 2
39 2
40 2
41 2
42 2
43 2
44 2
45 2
46 2
47 2
48 2
49 2
50 2
51 2
52 2
53 2
54 3
55 3
56 3
57 3
58 3
59 3
60 3
61 3
62 3
63 3
64 3
65 3
66 3
67 3
68 3
69 3
70 3
71 3
72 3
73 3
74 4
75 4
76 4
77 4
78 4
79 4
80 4
81 4
82 4
83 4
84 4
85 5
86 5
87 5
88 6
89 6
90 6
91 6
92 6
93 7
94 7
95 7
96 7
97 7
98 7
99 8
100 8
101 8
102 8
103 8
104 9
105 9
106 9
107 9
108 9
109 9
110 9
111 9
112 9
113 9
114 9
115 9
116 9
117 9
118 9
119 9
;
data junk;
input b cutpt1 cutpt2 cutpt3 cutpt4 cutpt5 cutpt6 cutpt7 cutpt8
cutpt9;
datalines;
31 22 53 73 84 87 92 98
103 109
50 22 53 73 84 87 92 98 103 109
71 22 53 73 84 87 92 98 103 109
80 22 53 73 84 87 92 98 103 109
90 22 53 73 84 87 92 98 103 109
68 22 53 73 84 87 92 98 103 109
90 22 53 73 84 87 92 98 103 109
90 22 53 73 84 87 92 98 103 109
5 22 53 73 84 87 92 98 103 109
2 22 53 73 84 87 92 98 103 109
;
run;
proc sort data=junk out=junk;
by b;

data test;
merge junk(in=j) lookup;
by b;
if j;
run;


On Mon, 11 Jan 2010 17:20:17 -0500, Francois van der Walt
<francoisw(a)GJI.COM.AU> wrote:

>Hi Ray,
>
>I would love to know what the background to the need is.
>
>One "pragmatic" solution will be to create a lookup table and then merge it
>with your junk table. Another solution would be to use formats
>
>Here is the lookup solution:
>
>data junk;
> input b cutpt1 cutpt2 cutpt3 cutpt4 cutpt5 cutpt6 cutpt7 cutpt8
>cutpt9;
> datalines;
> 31 22 53 73 84 87 92 98
> 103 109
>50 22 53 73 84 87 92 98 103 109
>71 22 53 73 84 87 92 98 103 109
>80 22 53 73 84 87 92 98 103 109
>90 22 53 73 84 87 92 98 103 109
>68 22 53 73 84 87 92 98 103 109
>90 22 53 73 84 87 92 98 103 109
>90 22 53 73 84 87 92 98 103 109
>5 22 53 73 84 87 92 98 103 109
>2 22 53 73 84 87 92 98 103 109
>;
>run;
>data test;
> set junk;
> if b le 22 then add_months= 1;
> if b gt 22 and b le 53 then add_months=2;
> if b gt 53 and b le 73 then add_months=3;
> if b gt 73 and b le 84 then add_months=4;
> if b gt 84 and b le 87 then add_months=5;
> if b gt 87 and b le 92 then add_months=6;
> if b gt 92 and b le 98 then add_months=7;
> if b gt 98 and b le 103 then add_months=8;
> if b gt 103 then add_months=9;
>run;
>
>Depending on your further need you can drop cutpt1 etc.
>
>Kind Regards
>Francois (Brisbane)
>
>On Mon, 11 Jan 2010 10:23:03 -0500, Ray Harvey <rharvey3015(a)GMAIL.COM> wrote:
>
>>I have a data table with variable 'b'. I want to compare variable 'b'
>>across a range of 9 values using a do loop. Depending on where
>>varible 'b' falls I will create a new variable called add_months. I can
>>program this manually but want to automate the process as the number or
>>comparisons (levels) will change in future iterations. Below is a sample
>>of the data and how I would achieve this manually.
>>
>>data junk;
>> input b cutpt1 cutpt2 cutpt3 cutpt4 cutpt5 cutpt6 cutpt7 cutpt8
>>cutpt9;
>> datalines;
>> 31 22 53 73 84 87 92 98
>> 103 109
>>50 22 53 73 84 87 92 98 103 109
>>71 22 53 73 84 87 92 98 103 109
>>80 22 53 73 84 87 92 98 103 109
>>90 22 53 73 84 87 92 98 103 109
>>68 22 53 73 84 87 92 98 103 109
>>90 22 53 73 84 87 92 98 103 109
>>90 22 53 73 84 87 92 98 103 109
>>5 22 53 73 84 87 92 98 103 109
>>2 22 53 73 84 87 92 98 103 109
>>;
>>run;
>>data test;
>> set junk;
>> if b le 22 then add_months= 1;
>> if b gt 22 and b le 53 then add_months=2;
>> if b gt 53 and b le 73 then add_months=3;
>> if b gt 73 and b le 84 then add_months=4;
>> if b gt 84 and b le 87 then add_months=5;
>> if b gt 87 and b le 92 then add_months=6;
>> if b gt 92 and b le 98 then add_months=7;
>> if b gt 98 and b le 103 then add_months=8;
>> if b gt 103 then add_months=9;
>>run;
 | 
Pages: 1
Prev: What concerns me the most about Enterprise Guide
Next: online SmartPLS SEM workshops in February