proc sql equivalent data step

[sfffdg]

Hi , 

what is the equivalent data step  for the below proc sql step.

proc sql;
create table test1 as
select GEO, pcode, state , suburb, street_name, street_number,
unit_number , typ , count(*) as cnt
from test
group by 1,2,3,4,5,6,7 ,8
having cnt >1;
quit;

I tried as below , but does not give the correct results

proc sort data=test;
by GEO pcode state  suburb street_name street_number
unit_number  typ ;
run;

data test1;
set TEST;
by GEO pcode state  suburb street_name street_number 

unit_number  typ ;
if first.GEO  ne  last.GEO then output ;
run;

[Tom]

You only asked the data step to output the first and last observation for the groups with more than 2 observations.

FIRST. and LAST. flags can only be 1 (true) or 0 (false).

They will be EQUAL when both are TRUE or both are FALSE.

The only observations you want to remove are when there is only one in the group, which is when both are TRUE.

If you want to group by all 8 BY variables then you need to test the flags for the last of the variables.  Testing the flags for just the first variable is equivalent to only grouping by that one variable in the SQL query.

if first.TYP and last.TYP then delete;

[PGStats]

Typo?

.... then OUTPUT;

[Tom]

---

@PGStats wrote:

Typo?

.... then OUTPUT;

---

Depends on whether you want to keep the unique observations or delete them.

The SQL query was deleting the groups with only one observation.