BookmarkSubscribeRSS Feed
sfffdg
Obsidian | Level 7

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;

3 REPLIES 3
Tom
Super User Tom
Super User

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
Opal | Level 21
Typo?

.... then OUTPUT;
PG
Tom
Super User Tom
Super User

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

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 468 views
  • 2 likes
  • 3 in conversation