topic Re: proc sql equivalent data step in SAS Programming

proc sql equivalent data step

sfffdg — Fri, 29 Apr 2022 02:18:05 GMT

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;

Re: proc sql equivalent data step

Tom — Fri, 29 Apr 2022 03:00:56 GMT

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;

Re: proc sql equivalent data step

PGStats — Fri, 29 Apr 2022 03:12:34 GMT

Typo?

.... then OUTPUT;

Re: proc sql equivalent data step

Tom — Fri, 29 Apr 2022 03:22:08 GMT

@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.