Help using Base SAS procedures

Matching variable across observations in data step

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 19
Accepted Solution

Matching variable across observations in data step

I'm trying to figure out a way to compare the value of one variable with a different variable across different observations within a data step, and then output those observations where the data matches. I'm not quite sure if I'm going about this the right way, but I've been trying to do this with a hash table, where I essentially read the dataset in twice -- once in the hash table, and again through the data step. I'm then trying to find all observations in the hash table that match three different criteria -- where the PlantID and PrimeMover are the same, and where the primary fuel matches the secondary fuel. Here is my code so far:

DATA Test2;

if _N_=1 then do;

  declare hash h(dataset:'Test', MULTIDATA:'YES');

  h.definekey('PlantID','PrimeMover','Secondary');

  h.definedata('Primary','Secondary');

  h.Definedone();

end;

set Test;

if h.Find(keySmiley TonguelantID,keySmiley TonguerimeMover,keySmiley Tonguerimary)=0 then output;

RUN;

And here's some sample data:

YearPlantIDPrimeMoverPrimarySecondary
20123STNGNG
20123STNGFO2
20123STFO2NG
20123STNGBIT
20124HYWATWAT
20127STBITBIT
20127STBITNG
20127STNGBIT
20128STBITWC
20129GTNGDFO
20129GTNGNG
201210GTNGDFO
201210GTNGNG
201210STBITBIT
201210STBITWC
201226STDFODFO
201226STBITDFO
201226STBITBIT
201226STBITWC


If you take plant 3, for example, I want to output the first three observations, because the secondary fuel in the third observation matches the primary fuel in the first observation, and the secondary fuel in the second observation matches the primary fuel in the first observation. I don't care about the fourth observation because its secondary fuel is not overlapping with any of the primary fuels of the others. I know I'm not handling the hash multidata correctly at this point, but I'm mostly concerned with the find statement where I'm trying to get the observations where the secondary key matches the primary field of the current observation in the data step. I'm new to hashtags, so this may not be the best approach. I had also thought about writing a macro that would loop through the dataset twice to get those observations that match each other, but I figured I would try the hashtag approach first. Thanks for your help!


Accepted Solutions
Solution
‎08-27-2013 12:21 PM
Super User
Posts: 17,831

Re: Matching variable across observations in data step

Is this close to what you're looking for? PS. Hashtags are different than Hash Tables, which is different from Hash Smiley Happy

Basically join the table to itself and if the secondary matches the primary and the plant id is the same, but take only distinct records.

data have;

input Year    PlantID    PrimeMover $ Primary $    Secondary $;

cards;

2012    3    ST    NG    NG

2012    3    ST    NG    FO2

2012    3    ST    FO2    NG

2012    3    ST    NG    BIT

2012    4    HY    WAT    WAT

2012    7    ST    BIT    BIT

2012    7    ST    BIT    NG

2012    7    ST    NG    BIT

2012    8    ST    BIT    WC

2012    9    GT    NG    DFO

2012    9    GT    NG    NG

2012    10    GT    NG    DFO

2012    10    GT    NG    NG

2012    10    ST    BIT    BIT

2012    10    ST    BIT    WC

2012    26    ST    DFO    DFO

2012    26    ST    BIT    DFO

2012    26    ST    BIT    BIT

2012    26    ST    BIT    WC

;;;;

proc sql;

    create table want as

    select distinct a.*

    from have as a

    join have as b

    on a.Secondary=b.Primary

    and a.PlantID=b.PlantID

     and a.Primemover=b.primemover;

quit;

EDIT: add in condition for primemover to be the same as well as plant id.

View solution in original post


All Replies
Solution
‎08-27-2013 12:21 PM
Super User
Posts: 17,831

Re: Matching variable across observations in data step

Is this close to what you're looking for? PS. Hashtags are different than Hash Tables, which is different from Hash Smiley Happy

Basically join the table to itself and if the secondary matches the primary and the plant id is the same, but take only distinct records.

data have;

input Year    PlantID    PrimeMover $ Primary $    Secondary $;

cards;

2012    3    ST    NG    NG

2012    3    ST    NG    FO2

2012    3    ST    FO2    NG

2012    3    ST    NG    BIT

2012    4    HY    WAT    WAT

2012    7    ST    BIT    BIT

2012    7    ST    BIT    NG

2012    7    ST    NG    BIT

2012    8    ST    BIT    WC

2012    9    GT    NG    DFO

2012    9    GT    NG    NG

2012    10    GT    NG    DFO

2012    10    GT    NG    NG

2012    10    ST    BIT    BIT

2012    10    ST    BIT    WC

2012    26    ST    DFO    DFO

2012    26    ST    BIT    DFO

2012    26    ST    BIT    BIT

2012    26    ST    BIT    WC

;;;;

proc sql;

    create table want as

    select distinct a.*

    from have as a

    join have as b

    on a.Secondary=b.Primary

    and a.PlantID=b.PlantID

     and a.Primemover=b.primemover;

quit;

EDIT: add in condition for primemover to be the same as well as plant id.

Occasional Contributor
Posts: 19

Re: Matching variable across observations in data step

Thanks for this solution. I ended up using a different SQL approach where I counted the duplicate secondary sources within each primary source. This had the added benefit of giving me the number of overlapping sources, which I will need for later analysis.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 222 views
  • 0 likes
  • 2 in conversation