topic data pick in SAS Programming

data pick

helloSAS — Fri, 09 May 2014 01:42:55 GMT

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

Re: data pick

Reeza — Fri, 09 May 2014 01:54:45 GMT

proc sort data = a;

by acct priority dst; run;

data b;

set a;

by acct priority dst;

if first.acct;

run;

Re: data pick

PGStats — Fri, 09 May 2014 02:14:10 GMT

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;

Re: data pick

helloSAS — Fri, 09 May 2014 02:15:36 GMT

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

3/9/2014

11/2/2014

Re: data pick

helloSAS — Fri, 09 May 2014 02:32:49 GMT

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'

Re: data pick

Reeza — Fri, 09 May 2014 02:52:56 GMT

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;

Re: data pick

PGStats — Fri, 09 May 2014 03:07:48 GMT

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;