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;
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;
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;
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 |
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?
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 |
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;
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);
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;
Many thanks! A big thumb to you!
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.
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!
Formats are definitely a more efficient approach in this case.
Yeah. Mr Miller's approaches are indeed among the best. 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.