BookmarkSubscribeRSS Feed
helloSAS
Obsidian | Level 7

Hi,

I've a source dataset like table a.  I need my resultant data to look like table b. The logic is, pick the record with higher priority # ,  but if the priority #'s are same I need to pick record where dst = 'N'. I wrote below code but it works only in the first acct and not for second account.

proc sort data = a;

by acct dst descending priority; run;

data b;

set a;

by acct dst descending priority;

if first.acct;

run;

Source - a

acct

zip

timezone

gmtoffset

dst

dst_start

dst_end

priority

areacode

9999

Mountain

-7

N

3/9/2014

11/2/2014

4

928

9999

Mountain

-7

N

3/9/2014

11/2/2014

4

928

9999

86033

Mountain

-7

Y

3/9/2014

11/2/2014

4

.

1111

00801

Atlantic

-4

N

3/9/2014

11/2/2014

1

.

1111

Mountain

-7

Y

3/9/2014

11/2/2014

4

720

Result - b

acct

zip

timezone

gmtoffset

dst

dst_start

dst_end

priority

areacode

9999

86033

Mountain

-7

Y

3/9/2014

11/2/2014

4

928

1111

Mountain

-7

Y

3/9/2014

11/2/2014

4

720

6 REPLIES 6
Reeza
Super User

proc sort data = a;

by acct  priority dst; run;

data b;

set a;

by acct priority dst;

if first.acct;

run;

helloSAS
Obsidian | Level 7

Reeza, Thanks!

wouldn't that select below record? I want the record with Mountain timezone

acct

zip

timezone

gmtoffset

dst

dst_start

dst_end

priority

areacode

1111

00801

Atlantic

-4

N

3/9/2014

11/2/2014

1

.

Reeza
Super User

Forgot the descending option:

proc sort data = a;

by acct  descending priority dst; run;

data b;

set a;

by acct descending priority dst;

if first.acct;

run;

PGStats
Opal | Level 21

Your example shows that you want the records with dst="Y", not "N".

One simple way to achieve this is to give a slight priority boost to records with dst="Y". I assume that there is at most one record with dst="Y" per acct and that priority # are integers:

proc sql;

create table b as

select * from a

group by acct

having priority+(dst="Y")/2 = max(priority+(dst="Y")/2);

select * from b;

quit;

PG

PG
helloSAS
Obsidian | Level 7

It is not safe to assume that there will be at most one record with dst="Y". I might have all records in an account that have dst='N'

PGStats
Opal | Level 21

Your problem is not stated very clearly, so I made some assumptions. For every acct you want:

1) the record with the highest priority

2) in case of a tie in priority, the record with dst = "N"

3) in case of a tie in priority and dst, the record with timezone="Mountain"

4) in case of a tie in priority, dst and timezone, any one of the tied records

proc sql;

create table b as

select unique * from a

group by acct

having priority+(dst="N")/2+(timezone="Mountain")/4 =

  max(priority+(dst="N")/2+(timezone="Mountain")/4);

select * from b;

quit;

PG

PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 6 replies
  • 1034 views
  • 0 likes
  • 3 in conversation