BookmarkSubscribeRSS Feed
TM
Calcite | Level 5 TM
Calcite | Level 5

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

8 REPLIES 8
LinusH
Tourmaline | Level 20

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

/Linus

Data never sleeps
TM
Calcite | Level 5 TM
Calcite | Level 5

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
SAS Employee

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
    Rhodochrosite | Level 12

    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
    Rhodochrosite | Level 12

    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
    Super User

    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
    Calcite | Level 5

    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
    Super User

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

    sas-innovate-2024.png

    Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

    Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

     

    Register now!

    What is Bayesian Analysis?

    Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

    Find more tutorials on the SAS Users YouTube channel.

    Click image to register for webinarClick image to register for webinar

    Classroom Training Available!

    Select SAS Training centers are offering in-person courses. View upcoming courses for:

    View all other training opportunities.

    Discussion stats
    • 8 replies
    • 1030 views
    • 0 likes
    • 7 in conversation