How to use correctly the SET with the KEY= option statement?

Discussion in 'SAS (Statistical Analysis Software)' started by alejandro, Aug 16, 2004.

  1. alejandro

    alejandro Guest

    Sorry I commit some heedlessness in my post. I have corrected below.

    Hi everybody,

    I have a little problem understanding the set key=option. I hope that
    someone could help me.

    Here is my problem.

    I have two datasets:
    - Dataone having 2 columns , say A and dt.
    - Datatwo having 3 columns , say A, tmin,and tmax.

    I want to combine this two datasets, in that sense:
    for each value of A for the table DataOne, I have to look up into all
    possible observations of Datatwo for the same value of A, for wich
    tmin <= dt <= tmax.

    It is not possible to use the merge statement, since for a same value
    of A in dataOne, there is several values in DataTwo.

    For dowing this, I know I can use an sql code, but I prefer to avoid
    it here.

    Here is an exemple of the two datasets and the dataset wich I want to
    obtain.


    data DataOne;
    input A dt ;
    cards;
    1 5
    1 6
    1 7
    1 9
    ;
    run;

    data DataTwo(index=(A));
    input A tmin tmax ;
    cards;
    1 1 2
    1 8 10
    1 4 6
    1 2 3
    ;
    run;

    I Want to obtain a dataset Result
    A dt tmin tmax
    1 5 4 6
    1 6 4 6
    1 7 . .
    1 9 8 10


    Data Result;
    set DataOne;
    do until (_iorc_ = %sysrc(_dsenom));
    set Datatwo key=A;

    select(_iorc_);
    when(%sysrc(_sok)) do;
    if (tmin le dt le tmax) then do;
    output;
    end;
    end;
    when(%sysrc(_dsenom)) do;
    _error_ = 0;
    end;
    otherwise do;
    put 'Unexpected ERROR: _IORC_ = ' _iorc_;
    stop;
    end;
    end;
    end;
    run;


    594 Data Result;
    595 set DataOne;
    596 do until (_iorc_ = %sysrc(_dsenom));
    597 set Datatwo key=A;
    598 select(_iorc_);
    599 when(%sysrc(_sok)) do;
    600 if (tmin le dt le tmax) then do;
    601 output;
    602 end;
    603 end;
    604 when(%sysrc(_dsenom)) do;
    605 _error_ = 0;
    606 end;
    607 otherwise do;
    608 put 'Unexpected ERROR: _IORC_ = ' _iorc_;
    609 stop;
    610 end;
    611 end;
    612 end;
    613 run;

    NOTE: DATA statement used:
    real time 20.65 seconds
    cpu time 0.60 seconds

    NOTE: There were 4 observations read from the data set WORK.DATAONE.
    NOTE: The data set WORK.RESULT has 1 observations and 4 variables.

    Result has one observation and none observation has been read from
    Datatwo!!

    Can some help me and tell me what is wrong?

    Thanks in advance.

    Alex
     
    alejandro, Aug 16, 2004
    #1
    1. Advertisements

  2. Alex:

    A part of the the problem is that you need to rewind or start the range
    lookup over again. This is because your lookup key is not unique.

    The SET KEY= wasn't really designed with 'rewind' in mind, but you can fake
    it out by forcing a change in key that would never match, doing the SET (and
    failing) and then changing back to the original key so that SET KEY= locates
    at the start of the matching records.
    ----
    data DataOne;
    input A dt ;
    cards;
    1 5
    1 6
    1 7
    1 9
    2 3
    3 7
    ;
    run;

    data DataTwo(index=(A));
    input A tmin tmax ;
    cards;
    1 1 2
    1 8 10
    1 4 6
    1 2 3
    2 1 2
    2 3 4
    ;
    run;


    * output only DataOne that are in a dt range specified in DataTwo;

    Data Result;
    set DataOne ;
    put a= dt=;

    need_rewind=a eq lag(a);
    range_found = 0;
    do until (_iorc_ = %sysrc(_dsenom) or range_found);

    do i = 0 to need_rewind;
    if i<need_rewind then do;
    olda=a;
    a=.;
    end;
    set Datatwo key=A;
    if need_rewind then do;
    _error_=0;
    a=olda;
    need_rewind=0;
    end;
    end;

    put _iorc_= a= tmin= tmax=;

    select(_iorc_);
    when(%sysrc(_sok)) do;
    if (tmin le dt le tmax) then do;
    range_found=1;
    output;
    end;
    end;
    when(%sysrc(_dsenom)) do;
    _error_ = 0;
    end;
    otherwise do;
    put 'Unexpected ERROR: _IORC_ = ' _iorc_;
    stop;
    end;
    end;
    end;

    drop need_rewind range_found i olda;
    run;
    ----

    Search google for other discussions.
    http://groups.google.com/groups?q=set+key=&meta=group=comp.soft-sys.sas


    Richard A. DeVenezia
    Done being productive? Try SAS Tetris.
    http://www.devenezia.com/downloads/sas/af?topic=27
     
    Richard A. DeVenezia, Aug 17, 2004
    #2
    1. Advertisements

  3. alejandro

    alejandro Guest

    Thank you very much Richard for your valuable help.
    Have a good day.
    Alex
     
    alejandro, Aug 17, 2004
    #3
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.