## Proc Sort unique pairs

Solved
Occasional Contributor
Posts: 15

# 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
Posts: 9,599

## Re: Proc Sort unique pairs

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;

All Replies
Occasional Contributor
Posts: 8

## Re: Proc Sort unique pairs

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

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
Posts: 9,599

## Re: Proc Sort unique pairs

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

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

delete the comma behind __periodEnd,__

Super User
Posts: 10,778

## Re: Proc Sort unique pairs

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: 10,239

## Re: Proc Sort unique pairs

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
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 15