Proc Sort unique pairs

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 15
Accepted Solution

Proc Sort unique pairs

Hello Everyone!

I cannot figure out how to get particular set of data from a data basis.

My Data base looks like this

Company,PeriodEnd,PeriodIndicator

A,1999,1

A,1999,2

A,2001,1

A,2002,1

A,2002,2

A,2003,2

I want to get:

A,1999,1

A,1999,2

A,2002,1

A,2002,2

So, de facto observations where the period indicator for a given year takes both values.

I would be very thankful for your help.


Accepted Solutions
Solution
‎05-12-2014 06:14 AM
Super User
Super User
Posts: 7,942

Re: Proc Sort unique pairs

Posted in reply to RichardinOz

Just out of interest, another method:

data have;
  attrib Company PeriodEnd PeriodIndicator format=$20.;
  infile datalines delimiter=",";
  input company $ periodend $ periodindicator $;
datalines;
A,1999,1
A,1999,2
A,2001,1
A,2002,1
A,2002,2
A,2003,2
;
run;

proc sql;
  create table WORK.WANT as
  select  distinct *
  from    WORK.HAVE A
  where   PERIODINDICATOR="1" and exists(select THIS.COMPANY from WORK.HAVE THIS where THIS.COMPANY=A.COMPANY and THIS.PERIODEND=A.PERIODEND and THIS.PERIODINDICATOR="2")
    or    PERIODINDICATOR="2" and exists(select THIS.COMPANY from WORK.HAVE THIS where THIS.COMPANY=A.COMPANY and THIS.PERIODEND=A.PERIODEND and THIS.PERIODINDICATOR="1");
quit;

View solution in original post


All Replies
Occasional Contributor
Posts: 8

Re: Proc Sort unique pairs

Posted in reply to MattvanPallen

select company

         , periodEnd

          , PeriodInidicator

          , count(*) as flag

     from *input*

     group by company, periodEnd

     having flag eq 2 and PeriodIndicator in (1,2)

Super Contributor
Posts: 644

Re: Proc Sort unique pairs

Posted in reply to sdoorneveld

Depending on whether the data contains duplicates you might need to use count (Distinct Company)

Richard

Solution
‎05-12-2014 06:14 AM
Super User
Super User
Posts: 7,942

Re: Proc Sort unique pairs

Posted in reply to RichardinOz

Just out of interest, another method:

data have;
  attrib Company PeriodEnd PeriodIndicator format=$20.;
  infile datalines delimiter=",";
  input company $ periodend $ periodindicator $;
datalines;
A,1999,1
A,1999,2
A,2001,1
A,2002,1
A,2002,2
A,2003,2
;
run;

proc sql;
  create table WORK.WANT as
  select  distinct *
  from    WORK.HAVE A
  where   PERIODINDICATOR="1" and exists(select THIS.COMPANY from WORK.HAVE THIS where THIS.COMPANY=A.COMPANY and THIS.PERIODEND=A.PERIODEND and THIS.PERIODINDICATOR="2")
    or    PERIODINDICATOR="2" and exists(select THIS.COMPANY from WORK.HAVE THIS where THIS.COMPANY=A.COMPANY and THIS.PERIODEND=A.PERIODEND and THIS.PERIODINDICATOR="1");
quit;

Occasional Contributor
Posts: 15

Re: Proc Sort unique pairs

Posted in reply to sdoorneveld

proc sql;

create table base2

as select company, periodEnd, PeriodIndicator, count(*) as flag

     from base2

     group by company, periodEnd,

  having flag eq 2 and PeriodIndicator in (1,2);

quit;

is it right?

I get syntax error with "flag"

having flag eq 2 and PeriodIndicator in (1,2);

           ----

          76

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, '.',

              /, <, <=, <>, =, >, >=, AND, EQ, EQT, GE, GET, GT, GTT, HAVING, LE, LET, LT, LTT,

              NE, NET, OR, ORDER, ^=, |, ||, ~=.

Occasional Contributor
Posts: 8

Re: Proc Sort unique pairs

Posted in reply to MattvanPallen

delete the comma behind __periodEnd,__

Super User
Posts: 10,018

Re: Proc Sort unique pairs

Posted in reply to MattvanPallen

If I understand what you mean .

data have;
  attrib Company PeriodEnd PeriodIndicator format=$20.;
  infile datalines delimiter=",";
  input company $ periodend $ periodindicator $;
datalines;
A,1999,1
A,1999,2
A,2001,1
A,2002,1
A,2002,2
A,2003,2
;
run;
proc sql;
  create table WORK.WANT as
  select company, periodEnd, PeriodIndicator
     from have
     group by company, periodEnd
      having count(distinct PeriodIndicator ) eq 2;
quit;

Xia Keshan

Super User
Posts: 7,757

Re: Proc Sort unique pairs

Posted in reply to MattvanPallen

Another suggestion for doing it in a data step, utilizing a "dow loop":

data want (drop=indicator);
indicator = 0;

do until (last.periodend);

  set have;

  by company periodend;

  if periodindicator = "1" then indicator = 1;

  if periodindicator = "2" and indicator = 1 then indicator = 2;

end;

do until (last.periodend);

  set have;

  by company periodend;

  if indicator = 2 then output;

end;

run;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 15

Re: Proc Sort unique pairs

Posted in reply to MattvanPallen

Thank you guys!

🔒 This topic is solved and locked.

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

Discussion stats
  • 8 replies
  • 313 views
  • 10 likes
  • 6 in conversation