BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Chris_LK_87
Quartz | Level 8

Hello!

 

I have a dataset that is sorted in id, years, months, day and city based  for each individual purchase. I want to sorted out duplicates that have the same id, year, month and day but different city as shown in dataset 'want'. 

 

data have;
;
input ID$ Year$ month$ Day$ City$;
datalines;
1 2017 Jan Monday Rome
1 2017 Jan Monday Paris
1 2018 Jan Tuesday Rome
1 2018 Jan Tuesday Rome
2 2015 Feb Wednesday Rome
2 2015 Feb Wednesday Paris
2 2018 Feb Friday Rome
2 2018 Feb Friday Rome
3 2015 Apr Wednesday Rome
3 2015 Apr Wednesday Paris
3 2018 Apr Friday Rome
3 2018 Apr Friday Rome
;
run;

 

data want;
;
input ID$ Year$ month$ Day$ City$;
datalines;
1 2017 Jan Monday Rome
1 2017 Jan Monday Paris
2 2015 Feb Wednesday Rome
2 2015 Feb Wednesday Paris
3 2015 Apr Wednesday Rome
3 2015 Apr Wednesday Paris
;
run;

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
data want;
    set have;
    by id year month day city;
    if first.city and last.city;
run;

Your data is year/month/day of week instead of year/month/day of month? That really doesn't make sense to me.

--
Paige Miller

View solution in original post

2 REPLIES 2
PaigeMiller
Diamond | Level 26
data want;
    set have;
    by id year month day city;
    if first.city and last.city;
run;

Your data is year/month/day of week instead of year/month/day of month? That really doesn't make sense to me.

--
Paige Miller
Ksharp
Super User
 

data have;;
input ID$ Year$ month$ Day$ City$;
datalines;
1 2017 Jan Monday Rome
1 2017 Jan Monday Paris
1 2018 Jan Tuesday Rome
1 2018 Jan Tuesday Rome
2 2015 Feb Wednesday Rome
2 2015 Feb Wednesday Paris
2 2018 Feb Friday Rome
2 2018 Feb Friday Rome
3 2015 Apr Wednesday Rome
3 2015 Apr Wednesday Paris
3 2018 Apr Friday Rome
3 2018 Apr Friday Rome
;
run;

proc sort data=have out=duplicate nouniquekey uniqueout=want;
by _all_;
run;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 2 replies
  • 267 views
  • 1 like
  • 3 in conversation