How to get min value in this condition

[superbug]

I have data as in the attached.

When sscore=200, I want the sscore_sem=88, which is the smallest number of s_score_se (take integer) when sscore=200.

When sscore=800, I want sscore_sem=86, which is the smallest number of s_score_se (take integer) when sscore=800. 

when 200<sscore<800, I want sscore_sem=INT(s_score_se).

The following SAS code does not work for sscore=200 or sscore=800 conditions. please help. thanks! 

data try1;
set try;
if SScore=200 then SScore_SEM=INT(MIN(S_Score_se));
else if SScore=800 then SScore_SEM=INT(MIN(S_score_se));
else if 200 <SScore <800 then SScore_SEM=INT(S_Score_se);
run;

[Reeza]

MIN() works on a single row only.

If you want summary statistics by group, create a grouping variable and then use PROC MEANS instead. or PROC SQL

data try1;
set try;

if SScore=200 then SScore_SEM="200";
else if SScore=800 then SScore_SEM="800";
else if 200 <SScore <800 then SScore_SEM="200 to 800";
else SScore_SEM="CHECK ME" ;

run;

proc freq data=try1;
table sscore_sem;
run;

proc means data=try1 N SUM MIN;
class sscore_sem;
var s_score_se;
output out=want min=min_value;
run;

---

@superbug wrote:

I have data as in the attached.

When sscore=200, I want the sscore_sem=88, which is the smallest number of s_score_se (take integer) when sscore=200.

When sscore=800, I want sscore_sem=86, which is the smallest number of s_score_se (take integer) when sscore=800. 

when 200<sscore<800, I want sscore_sem=INT(s_score_se).

The following SAS code does not work for sscore=200 or sscore=800 conditions. please help. thanks! 

data try1;
set try;
if SScore=200 then SScore_SEM=INT(MIN(S_Score_se));
else if SScore=800 then SScore_SEM=INT(MIN(S_score_se));
else if 200 <SScore <800 then SScore_SEM=INT(S_Score_se);
run;

---

[superbug]

@Reeza @novinosrin 

Thanks much for the reply!

I might not state my question clearly. As in the table below, "want" column is what I wanted based on the first two columns. Basically if SScore=200, "want" will take the smallest number(integer value) in the "SScore_se" column.  How should I get that?

SScore_se	SScore	want
165.1214	200	88
119.3441	200	88
88.05075	200	88

[novinosrin]

Hi @superbug  How I wish you posted your expected output for your input sample. So do you mean you want the min(int(SScore_se)  value to remerge with the original dataset?

[superbug]

@novinosrin 

 It seems I made a simple question become complex. In the table below, the first two columns are my input file. In the output file, I like the "want" column to be added. Values in "want" column is the smallest number (take integer) of the "SScore_se" column. I used the following code, but it does not work. please help. thanks!

if SScore=200 then want=INT(MIN(SScore_se);

SScore_se	SScore	want
165.1214	200	88
119.3441	200	88
88.05075	200	88

[novinosrin]

Holy Jesus!, Right I hope I understood the requirement this time

proc sql;
create table want as
select *,min(int(SScore_se)) as want
from have
group by SScore;
quit;

[superbug]

@novinosrin 

Thanks a bunch!

I have some other condition when defining "want". As table below

SScore_se	SScore	want
165.1214	200	88
119.3441	200	88
88.05075	200	88
86.90429	800	86
118.478	800	86
164.494	800	86

could you please help incorporate the following condition to your code?

if sscore=200 or sscore=800 then want=INT(MIN(sscore_se));

if 200<sscore<800 then want=INT(sscore_se);

[novinosrin · Accepted solution]

 
data have;
input SScore_se	SScore;*	want;
cards;
165.1214	200	88
119.3441	200	88
88.05075	200	88
86.90429	800	86
118.478	    800	86
164.494	    800	86
87.90429	500	86
119.478	    500	86
165.494	    500	86
;
proc sql;
create table want(drop=grp) as
select *,case when sscore in (200,800) or 200<sscore<800 then sscore
else 0 end as grp,min(int(SScore_se)) as want
from have
group by grp;
quit;

[Reeza]

It looks like overall you're trying to add summary group statistics to your main data set. There are two standard ways to do this and both are illustrated here with full examples. You'll need to change the statistics to the ones you want to calculate but both approaches are valid.

https://github.com/statgeek/SAS-Tutorials/blob/master/add_average_value_to_dataset.sas

[superbug]

@novinosrin 

Many thanks! A big thumb to you!

[Reeza]

Replace the N/SUM in the PROC MEANS with MIN or MAX or whatever statistics you want. Look at the documentation for PROC MEANS to find all the statistics available.

[novinosrin]

Hi @superbug Following  @PaigeMiller 's shoes, you could consider defining formats and do proc summary using formatted values as groups in the class statement. Well, up to you!

[Reeza]

Formats are definitely a more efficient approach in this case. 

[novinosrin]

Yeah. Mr Miller's approaches are indeed among the best. 🙂