topic Re: assigning code based on score in SAS Programming

assigning code based on score

kpdoe — Thu, 22 Apr 2021 15:26:13 GMT

Hello I need help with assigning tag for high score by variable "A" taking a priority regardless of the high score in var "B". With below code, I am not getting the correct output.

I have a data: testa

ID	secnd_ID	A	B	Score
12345	34557		1	0.00514
12345	23456	3		0.00449
12345	34545		1	0.00384

Output with code below:

ID	secnd_ID	A	B	Score	Cnt	Tag	Code
12345	34557		1	0.00514	1
12345	23456	3		0.00449	2	D	Delete
12345	34545		1	0.00384	3	D	Delete

Desired output:

ID	secnd_ID	A	B	Score	Cnt	Tag	Code
12345	34557		1	0.00514	2	D	Delete
12345	23456	3		0.00449	1
12345	34545		1	0.00384	3	D	Delete

Code:

proc sort data=testa;
by ID descending score descending A;
run;


data test_keep
 set testa;
by id;
retain cnt;
if first.id = 1 then cnt = 0;
cnt + 1;
if cnt > 1 then tag = 'D'
if tag = 'D' then code = 'Delete';
run;

Re: assigning code based on score

ballardw — Thu, 22 Apr 2021 15:31:46 GMT

@kpdoe wrote:

Hello I need help with assigning tag for high score by variable "A" taking a priority regardless of the high score in var "B". With below code, I am not getting the correct output.

Your code does nothing with the value of variable A so how is a "high score by variable A" determined or evaluated?

What exactly does "taking a priority" mean in this context?

Re: assigning code based on score

kpdoe — Thu, 22 Apr 2021 15:36:37 GMT

Meaning if there's a value in var "A" there should be no tag and code assigned.

Re: assigning code based on score

Astounding — Thu, 22 Apr 2021 17:22:02 GMT

It sounds like you're close. Try replacing this statement:

set testa;

Instead, use this and see if it does what you need:

set testa (where=(A > .)) testa (where=(A=.));

Re: assigning code based on score

kpdoe — Thu, 22 Apr 2021 18:21:49 GMT

@Astoundingthat worked but, I still want a record with the highest score. For the above scenario the code worked but did not satisfy this one.

Output with your code below:

ID	secnd_ID	A	B	Score	Cnt	Tag	Code
67890	35456	001		0.01567	1
67890	37459	004		0.04237	2	D	Delete
67890	75456		001	0.00958	3	D	Delete

Desired output: I need the output with the higher score

ID	secnd_ID	A	B	Score	Cnt	Tag	Code
67890	37459	004		0.04237	1
67890	35456	001		0.01567	2	D	Delete
67890	75456		001	0.00958	3	D	Delete

Re: assigning code based on score

Astounding — Thu, 22 Apr 2021 19:20:45 GMT

The code looks OK. Show the log of the program that produced the incorrect results.

Re: assigning code based on score

Tom — Thu, 22 Apr 2021 19:49:34 GMT

This works with your example:

data have;
  input ID secnd_ID A B Score;
cards;
12345 34557 . 1 0.00514
12345 23456 3 . 0.00449
12345 34545 . 1 0.00384
;

proc sort data=have ;
  by id descending score ;
run;

data want;
  set have(where=(not missing(a))) have(where=(missing(a)));
  by id;
  cnt+1;
  if first.id then cnt=1;
  else do;
    tag='D'; code='Delete';
  end;
run;

If you want it to print in the same order then re-sort it.

proc sort data=want ;
  by id descending score ;
run;

Obs      ID     secnd_ID    A    B     Score    cnt    tag     code

 1     12345      34557     .    1    .00514     2      D     Delete
 2     12345      23456     3    .    .00449     1
 3     12345      34545     .    1    .00384     3      D     Delete