Datastep, conditional keep

Occasional Contributor
Posts: 7

Datastep, conditional keep

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

Super User
Posts: 5,888

Datastep, conditional keep

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

/Linus

Data never sleeps
Occasional Contributor
Posts: 7

Re: Datastep, conditional keep

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!

Posts: 1,318

Re: Datastep, conditional keep

`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          change431                                                                                                                                                                                                                                                                                                                                                    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 Normal2                                                                                                              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 Trial9                                                                                                                                            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 Normal16                                                                                                                                                                                                                                                                              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 Normal21                                                                                                                                                                                                                                                          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 Normal24          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 Normal28          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 Normal39                                                                      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 Normal41                                                                                                    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 Normal45                                                                                                                                                                                                                            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 Trial53                                                                                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 Normal60                                                                                          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 Trial67          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 Trial76                    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 Normal93                                                                                                                                                                                                                                                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.

Valued Guide
Posts: 2,191

Re: Datastep, conditional keep

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 ;

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.

Valued Guide
Posts: 765

Re: Datastep, conditional keep

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;

Super User
Posts: 10,787

Re: Datastep, conditional keep

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

Contributor
Posts: 43

Re: Datastep, conditional keep

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:

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

Hope you enjoy :-)

Super User
Posts: 10,787