BookmarkSubscribeRSS Feed
sophia_SAS
Obsidian | Level 7

SAS users,

Using Proc freq, I would like to identify which students received a grade A (where A=1) in year 1, year 2 and year 3 and I would like to sort it by school.

Here's a sample of my code:

proc sort data=have;

by school;

proc freq data=have;

tables year;

where grade=1;

by school;

run;

However I don't know how to specify that the student must have an A (grade=1) in all years, not just one.

Thanks.

8 REPLIES 8
ballardw
Super User

If GRADE=1 means a grade of A your tables should only be looking grades of A regardless of year.

You might want to use TABLE SCHOOL*YEAR / LIST; if you want a single table sorted by school. HINT: Freq display the output in order without a separate sort using the formatted values of a variable.

If you don't think the where is working you can test your code by putting the grade variable in the table statement.

TABLE SCHOOL*YEAR*GRADE / LIST;


sophia_SAS
Obsidian | Level 7

Thank you.  However I'm not sure how I an see which students received an A in all three years.  I would like to track individual students rather than the overall percentage of students who received an A. (I.e. who received an A in both year 1 and year 2).  Maybe I'm missing something?

Linlin
Lapis Lazuli | Level 10

you can create a dataset with students who got As in all three years.

data have;

input id school year grade;

cards;

1 1 1 1

1 1 2 2

1 1 3 1

2 1 1 1

2 1 2 1

2 1 3 1

1 2 1 1

1 2 2 2

1 2 3 1

5 2 1 1

5 2 2 1

5 2 3 1

;

proc sql;

  create table want as select *, sum(grade) as total

    from have

        group by school,id

          having total=3

           order by school;

quit;

proc print;run;

MikeZdeb
Rhodochrosite | Level 12

hi ... this makes some fake data with variables SCHOOL, STUDENT, YEAR, and GRADE then uses SQL to find students within schools with A in all three years (RANTBL ... 30% chance that student gets an A in any given year) ...

data have;

do year = 2008 to 2010;

do school = 1 to 5;

do student = 1 to 100;

   grade = rantbl(999,0.3,0.5);

   output;

end; end; end;

run;

proc sql;

create table gradea as

select school, student from have (where=(grade eq 1))

group by school, student

having count(*) eq 3

order school, student;

quit;

school=1

student

   32

   33

   35

   87

   92

school=2

student

   52

school=3

student

   16

   62

school=4

student

    4

   19

   58

   75

Linlin
Lapis Lazuli | Level 10

Hi Mike,

how did "rantbl(999,0.3,0.5)" assign value 1,2,3 to grade?

Thanks - Linlin

MikeZdeb
Rhodochrosite | Level 12

Hi ... 1st argument is a seed, remaining arguments assign 1, 2, 3, 4, etc with the probability you specify ...

a/ probabilities do not sum to 1, RANTBL sums those specified and subtracts them from 1 to get one more assignment rule

b/ probability sum  > 1, RANTBL adjusts/ignores stated probabilities

data test;

do _n_ = 1 to 1e5;

   x = rantbl(0, 0.3, 0.4, 0.25);

   y = rantbl(0, 0.3, 0.4, 0.25,  0.05);

   z = rantbl(0, 0.3, 0.4, 0.35,  0.35);

   output;

end;

run;

                              Cumulative    Cumulative

x    Frequency     Percent     Frequency      Percent

1       30118       30.12         30118        30.12

2       39952       39.95         70070        70.07

3       24898       24.90         94968        94.97

4        5032        5.03        100000       100.00

                              Cumulative    Cumulative

y    Frequency     Percent     Frequency      Percent

1       30010       30.01         30010        30.01

2       39996       40.00         70006        70.01

3       25022       25.02         95028        95.03

4        4972        4.97        100000       100.00

                              Cumulative    Cumulative

z    Frequency     Percent     Frequency      Percent

1       30136       30.14         30136        30.14

2       39908       39.91         70044        70.04

3       29956       29.96        100000       100.00

Another use is to randomly assign a value from an array to a variable ...

array names(5) $6 _temporary_ ('mike' 'linlin' 'ksharp' 'art' 'peterc');

name = names(rantbl(0, 0.2, 0.2, 0.2, 0.2));

                                   Cumulative    Cumulative

name      Frequency     Percent     Frequency      Percent

art          19869       19.87         19869        19.87

ksharp       19903       19.90         39772        39.77

linlin       20122       20.12         59894        59.89

mike         20089       20.09         79983        79.98

peterc       20017       20.02        100000       100.00

FYI ... there's also CALL RANTBL, and from online doc ...

The CALL RANTBL routine gives greater control of the seed and random number streams than does the RANTBL function.

Linlin
Lapis Lazuli | Level 10

Thank you Mike! I understand now.

if we change "grade = rantbl(999,0.3,0.5);" to

"grade = rantbl(999,0.3,0.7);" then grade would be 1,2.

to

"grade = rantbl(999,0.3,0.4,0.2);" then grade would be 1,2,3,4.

Thanks - Linlin

Ksharp
Super User

Assuming You have no replicated observations .

data have;
input id school year grade;
cards;
1 1 1 1
1 1 2 2
1 1 3 1
2 1 1 1
2 1 2 1
2 1 3 1
1 2 1 1
1 2 2 2
1 2 3 1
5 2 1 1
5 2 2 1
5 2 3 1
;
run;
proc sort data=have;by school; run;

proc freq data=have noprint;
where grade=1;
tables school*id/out=want(drop=percent where=(count=3)) nocum nopercent;
run;

Ksharp

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1512 views
  • 2 likes
  • 5 in conversation