topic Re: How to get min value in this condition in SAS Programming

How to get min value in this condition

superbug — Fri, 15 May 2020 19:39:56 GMT

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;

Re: How to get min value in this condition

Reeza — Fri, 15 May 2020 21:56:41 GMT

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;

Re: How to get min value in this condition

novinosrin — Fri, 15 May 2020 20:41:23 GMT

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!

Re: How to get min value in this condition

Reeza — Fri, 15 May 2020 20:44:42 GMT

Formats are definitely a more efficient approach in this case.

Re: How to get min value in this condition

superbug — Fri, 15 May 2020 20:47:04 GMT

@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

Re: How to get min value in this condition

novinosrin — Fri, 15 May 2020 20:48:01 GMT

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

Re: How to get min value in this condition

novinosrin — Fri, 15 May 2020 20:56:51 GMT

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?

Re: How to get min value in this condition

superbug — Fri, 15 May 2020 21:06:05 GMT

@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

Re: How to get min value in this condition

novinosrin — Fri, 15 May 2020 21:10:33 GMT

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;

Re: How to get min value in this condition

superbug — Fri, 15 May 2020 21:23:25 GMT

@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);

Re: How to get min value in this condition

novinosrin — Fri, 15 May 2020 21:41:55 GMT


 
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;

Re: How to get min value in this condition

Reeza — Fri, 15 May 2020 21:55:47 GMT

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.

Re: How to get min value in this condition

Reeza — Fri, 15 May 2020 21:58:02 GMT

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

Re: How to get min value in this condition

superbug — Fri, 15 May 2020 22:14:42 GMT

@novinosrin

Many thanks! A big thumb to you!