Datastep, conditional keep

[TM]

I have a problem that my feable datastep-skills simply can't seem to overcome.

I have data that looks like:

Iden     change1     change2     change3     change4     change5     ....     change44

1               a                x               x               z

2               a                x               x               x               x                         x

3               b                x               x

4               c                x               x               y

5               d                z               z               z               x                         x 

6                                 a               x               x               x                                 

.

.

1786

What i would like to do, is to identify all the IDEN's that have "a" or "b" in change1, and then "z", "y" or "empty" three weeks later. That is in change4.

In the above example, that would pull out IDEN 1,3,4 and 5.

Then calculate the percentage of the ones who had either "a" or "b" in change1, and then "z", "y" or "empty" three weeks later, out of everyone who had "a" or "b" in change1.

Exactly this, is what i would like to do in change1-change44. So the of the IDEN's in change2, who has "a" or "b", has "z", "y" or "empty" in change5.

And that i would like to do all the way to change40.

I hope that it's understandable..

/T

[LinusH]

Almost, but please attach some sample output data to avoid confusion...

/Linus

[TM]

Cool. Thanks..

Here is a link to some example data: http://dl.dropbox.com/u/1321324/TM%20example%20data.xlsx

The ones i'd like to calculate percentage of, are the ones who in changeX (X = i, 2...43), have a "Opened to Normal", "Campaign to Normal", "Trial to Normal" or "Free to Normal", and then in changeX+3, have "Closed from Normal" or " " (empty).

This i'd like to do running. From change2-change6, change3-change7, change4-change8 - and so forth.

If i take change21 as an example. There's two "Opened to Normal" and one "Campaign to Normal". Three weeks later there's two of them who are empty, thus 66,66 pct.

Thanks!

[FriedEgg]

proc format;
 invalue myifmt
  'Campaign to Campaign' = 0
  'Campaign to Normal'   = 1
  'Closed from Campaign' = 0
  'Closed from Free'     = 0
  'Closed from Normal'   = -1
  'Closed from Trial'    = 0
  'Free to Free'         = 0
  'Normal to Normal'     = 0
  'Opened to Campaign'   = 0
  'Opened to Free'       = 0
  'Opened to Normal'     = 1
  'Opened to Trial'      = 0
  'Trial to Trial'       = 0
 ;
 * These were all the distinct values in your example data, in your description you indicate there are additional to the list here;
run;
data foo;
 informat change2-change43 myifmt.;
 array change
 change2-change43;
 infile cards dsd dlm='09'x firstobs=2;
 input iden change2-change43;
 do i=1 to dim(change)-3;
  outp=0;
  do ii=1 to 3;
   if change>0 and change+ifn(missing(change[i+ii]),-1,change[i+ii])=0 then outp=i+1;
  end;
  if outp>0 then output;
 end;
 drop i ii change:;
 cards;
Iden          change2          change3          change4          change5          change6          change7          change8          change9          change10          change11          change12          change13          change14          change15          change16          change17          change18          change19          change20          change21          change22          change23          change24          change25          change26          change27          change28          change29          change30          change31          change32          change33          change34          change35          change36          change37          change38          change39          change40          change41          change42          change43
1                                                                                                                                                                                                                                                                                                                                                    Opened to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal
2                                                                                                              Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free          Opened to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Normal          Closed from Normal 
3                                                                                                                                                                                    Opened to Trial          Trial to Trial          Closed from Trial 
4                                                                                                                                                                                                        Opened to Normal          Normal to Normal          Closed from Normal 
5                                        Opened to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Closed from Campaign 
6                                                                                                                                                                                    Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Closed from Trial 
7                                                                                          Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
8                                                                                          Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free                                                                                                                                                                                    Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Trial to Trial
9                                                                                                                                            Opened to Trial          Trial to Trial          Trial to Trial          Closed from Trial 
10          Closed from Campaign 
11                                                                                                                                                                                                        Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Closed from Trial 
12                                                                                Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
13                                                                                                              Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
14                                                                                          Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
15                                                                                                                                                                                    Opened to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal
16                                                                                                                                                                                                                                                                              Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
17                                                                                                                                                                Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
18                                                                                          Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
19                                                  Opened to Campaign          Closed from Campaign 
20          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal
21                                                                                                                                                                                                                                                          Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
22                                                                                                                                            Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Closed from Trial 
23          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal
24          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Closed from Normal 
25                                                                                                    Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Closed from Trial 
26                                                                                                    Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Closed from Trial 
27          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal
28          Closed from Campaign 
29                                                                                Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Closed from Trial 
30                                                                                                                                                                                                                                                                                                                                                                                  Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Closed from Trial 
31                                                                                                    Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
32          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Closed from Normal 
33                                                                                                                                                                                                                                                                                                                      Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
34                                                                                                    Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
35                                                                                                                                                                                                                                                                                                                      Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Closed from Trial 
36                                                                                                                                                                                                                                                                                                            Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Closed from Trial 
37                                                                                                                                                                                                                                                                                                                                          Opened to Trial          Closed from Trial 
38                                                                                                                                            Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Closed from Trial                                                                                                                                                                                                                                      Opened to Normal          Normal to Normal
39                                                                      Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
40          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal
41                                                                                                    Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
42                                                                                                                                                                                                                                                                                                                                                                                  Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Closed from Trial 
43                                                                                                                                                                                                                                                                                                                                                              Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Closed from Trial 
44          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Closed from Normal                                                                      Opened to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal
45                                                                                                                                                                                                                            Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Closed from Trial 
46                                                                                                    Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Closed from Trial 
47          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Closed from Campaign 
48          Opened to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Closed from Campaign 
49          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Closed from Campaign 
50                                                                      Opened to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Closed from Campaign 
51                                                            Opened to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Closed from Campaign 
52                                                                                                                                                                                                                                                                                                                                                                                                                                    Opened to Trial
53                                                                                Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
54                                                                                          Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
55                                                                                                    Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
56                                                                                                                                                                                                                            Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Closed from Trial 
57                                                                                                                                            Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Closed from Trial 
58                                                                      Opened to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Closed from Campaign 
59                                        Opened to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal
60                                                                                          Opened to Campaign          Campaign to Campaign          Closed from Campaign 
61                                                                                                                                  Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
62                                                                                                                                                                                                                  Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
63                                                                      Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
64                                                                                                    Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
65                                                                                          Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
66                                                                                                                                                                                                                                                                                                                                                                                                                                    Opened to Trial
67          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Closed from Campaign 
68                                                                                          Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
69                                                                                                                                                                                                                                      Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Closed from Trial 
70                                                                      Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
71                                                                                                                                                                                                                                                                                                                                                                        Opened to Trial          Trial to Trial          Closed from Trial 
72                                                                                                    Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
73                                                                                                                                                                                                                                                                              Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Closed from Trial          Opened to Trial          Closed from Trial 
74                                                                                                                                                                                                                                                                                                                                                                                  Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Closed from Trial 
75                                                                                                                                                                          Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free                                                                                                                                  Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Trial to Trial
76                    Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
77                                                                                                                                                                                                                  Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
78                                                                                                                                                                                                        Opened to Normal          Normal to Normal          Closed from Normal 
79                                                                                                                                            Opened to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Closed from Campaign 
80                                                                      Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
81                                                                                                                                                                                                                                                Opened to Trial          Closed from Trial 
82                                                                                                                                                                                                                                                                                                  Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
83                                                                                                    Opened to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Closed from Normal 
84                                                                                Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
85                                                                                                                                                                                                                                                                                                  Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
86          Opened to Campaign          Closed from Campaign 
87                                                                                          Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
88                                                                                          Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
89                                                            Opened to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Closed from Campaign 
90                                                                                                                                            Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
91                                                                                          Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
92          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Closed from Normal
93                                                                                                                                                                                                                                                Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
94                                                                                                                                                                                                                                                                              Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Closed from Trial 
95                                                                                                              Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free          Opened to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Campaign          Campaign to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Closed from Normal 
96                                                                                                    Opened to Campaign          Closed from Campaign 
97                                                                                                                                                                                                                            Opened to Trial          Trial to Trial          Trial to Trial          Trial to Trial          Closed from Trial 
98          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Normal to Normal          Closed from Normal 
99                                                                                Opened to Free          Closed from Free                                        Opened to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Free to Free          Closed from Free 
;
run;

Here is the output:

                                                        Obs    iden    outp
                                                         1       2      29 
                                                         2       4      21 
                                                         3      78      21 

Variable outp is the period in which the change cycle started.

[Peter_C]

could only get that result with a bit of trouble and quite a few messages like

NOTE: SAS went to a new line when INPUT statement reached past the end of a line.

NOTE: Invalid data errors for file CARDS occurred outside the printed range.

NOTE: Increase available buffer lines with the INFILE n= option.

but got there

1. add OTHER=. range to the PROC FORMAT
2. set LRECL=1000 option on INFILE statement
3. save excel data as CSV
4. change  INFILE statement to:  

infile '..\TM example data.csv' dsd firstobs=2 lrecl=1000 ;

Still had "missing values"  message

NOTE: Missing values were generated as a result of performing an

operation on missing values.

Each place is given by: (Number of times) at (Line):(Column).

8904 at 1427:7

This NOTE disappears and results remain unchanged with the format range OTHER= 0

So that might be logical.

[MikeZdeb]

hi ... you say "X+3"  but then ask for change2-change6, etc. and that looks a lot like "X+4"

in that spreadsheet, change ranges from 2 to 43

set up two arrays as counters ... the 2nd part of each equation adds either 1 (TRUE) or 0 (FALSE) to the counters

after reading all the data, compute percentages

libname q 'z:\tm.xls';

data yy;

array a(2:39);

array b(2:39);

do until (done);

set q.'example$'n end=done;

array change(2:43);

do week=2 to 39;

   a(week) + (change(week) in ("Opened to Normal", "Campaign to Normal", "Trial to Normal" ,"Free to Normal") and

              change(week+4) in ("Closed from Normal" , " " ));

   b(week) + (change(week) in ("Opened to Normal", "Campaign to Normal", "Trial to Normal" ,"Free to Normal"));

end;

end;

do week=2 to 39;

   all = b(week);

   plus = a(week);

   if all gt 0 then pct = 100 * plus / all ;

   else pct = .;

   output;

end;

keep week pct all plus;

format pct 4.1;

run;

libname q clear;

[Ksharp]

It more looks like you need a macro.

data temp;
input Iden     (change1     change2     change3     change4     change5     change6 ) ($);
datalines;
1               a                x               x               z               .   z
2               a                x               a               x               x   x   
3               b                x               x               .               .   y
4               c                x               b               y               .   x
5               d                z               z               z               x   z   
6               .                a               x               x               x   . 
;
run;
%let dsid=%sysfunc(open(temp));
%let nvar=%sysfunc(attrn(&dsid,nvar));
%let dsid=%sysfunc(close(&dsid));
%let num_end=%eval(&nvar-4);

%macro per;
proc sql ;
 create table want(keep=per_: ) as
 select   

 %do i=1 %to &num_end;
  (select count(*) from temp where change&i in ('a' 'b')) as a&i,
  (select count(*) from temp where change%eval(&i+3) in ('z' 'y' ' ')) as b&i,
  calculated a&i/calculated b&i as per_change&i %if &i ne &num_end %then %do;,%end;
 %end;

from temp(obs=1);quit;
%mend per;
%per

Ksharp

[TMorville]

Thanks for the anwser guys. I think ill stick with a slightly changed version of Ksharp's macro.

Here's a little vid that you might enjoy:

http://www.youtube.com/watch?v=H1aaC7u__NM&sns=em

Start start text means "What if George Lucas had studied econometrics..." - Its from University of Copenhagen, The economic departments yearly show.

Hope you enjoy 🙂

[Ksharp]

I can't enter YouTube Twitter FaceBook ..... websites. China has forbidden these URL. It is very upset.