topic Re: How to get the rows having the minimum value of a variable if the minimum falls below a certain in SAS Programming

How to get the rows having the minimum value of a variable if the minimum falls below a certain rang

Shradha1 — Tue, 02 Feb 2021 07:01:19 GMT

I have a dataset with student names and their scores on different subject:

student a1 a2 a3 a4
jim 500 0 675 342
mary 345 45 876 510
mark 234 345 100 200
suzy 768 230 56 768

If the minimum score for any subject lies within 0-100(both values included) then I want to a flag for all those rows for that subject. If the minimum is higher than 100 then I want a flag only for the row with the minimum value. For example the resulting dataset will look like this:

student	a1	a2	a3	a4	flag_a1	flag_a2	flag_a3	flag_a4
jim	500	0	675	342		1
mary	345	45	876	510	1	1
mark	234	345	100	200			1	1
suzy	768	230	56	768			1

Lastly I want to filter out all the rows with flag =1:

student	flag_a1	flag_a2	flag_a3	flag_a4
jim		1
mary	1	1
mark			1	1
suzy			1

Any help would be great. Thank you!

Re: How to get the rows having the minimum value of a variable if the minimum falls below a certain

PeterClemmensen — Tue, 02 Feb 2021 05:16:38 GMT

Why is flag_a3 flagged in obs 2?

Re: How to get the rows having the minimum value of a variable if the minimum falls below a certain

Shradha1 — Tue, 02 Feb 2021 05:35:03 GMT

Sorry that was a mistake on my part. Have rectified the error

Re: How to get the rows having the minimum value of a variable if the minimum falls below a certain

PeterClemmensen — Tue, 02 Feb 2021 05:36:45 GMT

Ok. What about obs 3 and 4? Why only one flag?

Please be very specific about your logic 🙂

Re: How to get the rows having the minimum value of a variable if the minimum falls below a certain

ballardw — Tue, 02 Feb 2021 06:01:23 GMT

You need to explain this:

If the minimum is higher than 100 then I want a flag only for the row with the minimum value

or at least provide an example of which of these is getting flagged from this rule.

Do you have more than one row for a student in this this data? If so then you need to state so and provide an example of the data.

Some other things to consider:

Are any of the scores missing?

Do you want to consider missing as something to be flagged?

And it might not hurt to explain how those flags are to be used later. Sometimes it is a very good idea for "flags" to be coded 1/0. For example if you have the flags for Flag_a1 coded only as 1 if you want to determine something like "what percent of students had A1 flagged" you need to do a couple of steps because you don't have any easy to find "not flagged" information. But if the flag is 1/0 coded you can use any procedure, such as Means, Summary, Report or Tabulate that will calculate mean as the mean of 1/0 coded value is the percent of ones. Sum gives you the number of ones, or flags, if needed.

Re: How to get the rows having the minimum value of a variable if the minimum falls below a certain

Shradha1 — Tue, 02 Feb 2021 06:58:31 GMT

No there is no missing value in the data and only one row for each student

Re: How to get the rows having the minimum value of a variable if the minimum falls below a certain

Shradha1 — Tue, 02 Feb 2021 07:02:13 GMT

Please have a look at the updated question

Re: How to get the rows having the minimum value of a variable if the minimum falls below a certain

ballardw — Tue, 02 Feb 2021 15:07:57 GMT

@Shradha1 wrote:
No there is no missing value in the data and only one row for each student

Then this statement is basically meaningless:
If the minimum is higher than 100 then I want a flag only for the row with the minimum value

if there is only one row per student as there is only one row and every row has "the minimum value"