Course1 contains the counts information for students who have taken crs1000 to crs 2000. If the student haven’t taken the specific course, the value for the course of the student is 0. If the student have taken the specific course for multiple times, the value for the course is the total number of times the student took the course.
In following code, the array was set up to group the course of interest; count variable is used to capture number of distinct course taken from the selected courses; ifretake variable is used to track if student retakes any of the course from the selected courses.
data course2;
set course1;
array crs{*} crs1000 crs1010 crs1015 crs1200 crs1450 crs2000;
count=0;
ifretake =0;
do i =1 to dim(crs);
if crs{i}>0 then count = count+1;
if crs{i}>1 then ifretake = 1;
end;
run;
The following SAS codes get the filenames from L:\DaraFiles directory and pass them to a macro variable called varlist.
%let varlist =;
data null;
rc =filename("mydir", "L:\DataFiles");
did = dopen("mydir");
if did > 0 then do;
num =dnum(did);
do i = 1 to num;
fname =dread (did, i );
put fname = ;
call symput('varlist', trim(resolve('&varlist'))||' '||trim(fname));
end;
end;
run;
%put &varlist.;
Key Functions:
filename: assign the directory to a alias.
dopen: open the directory and get the directory identifier;
The National Collegiate Athletic Association (NCAA®) is a non-profit organization which regulates most of the United State’s college athletic programs, now supporting nearly half a million student athletes. They officially run the US Men’s and Women’s Division I college basketball tournament, March Madness®.
Google Cloud offers a suite of cloud computing services that runs on the same infrastructure that Google uses internally for its end-user products, like Google Search and YouTube. Google Cloud is now the official cloud of the NCAA®.
Kagglers who participate in this competition will use their computing power along with the NCAA®’s data to join the millions of fans who attempt to predict the outcomes of this year’s tournament.
The competition is split into 2 stages. In the first stage, Kagglers will rely on results of past tournaments to build and test models. In the second stage, competitors will forecast outcomes of all possible match-ups in the 2018 tournament. You don’t need to participate in the first stage to enter the second – the real competition is forecasting the 2018 results.
For example, data table contains student grades for the term. Each grade is one record and the term, program, student id information are the same for the student. The following data step can be used to order the data table and then concatenate the grade records into something like ‘A/A/B/B+/A’ for each student.
proc sort data= one;
by groupvar1 groupvar2 groupvar3;
run;
data two;
set one;
by groupvar1 groupvar2 groupvar3;
firstgroup = first.groupvar2;
lastgroup = last.groupvar2;
retain catvar;
if firstgroup =1 then catvar=trim(targetvar);
else catvar=catx('/',catvar, targetvar);
if lastgroup ne 1 then delete;
keep groupvar1 groupvar2 catvar;
run;
The following proc tabulate procedure will generate at a report on sum of undergraduate teaching and graduate teaching and FTE for each Faculty by departments (row header) and by year (column header).
proc tabulate data=data;
class year faculty dept;
var undergrad_load graduate_load fte;
table faculty, (dept all), year*(undergrad_load="UG" graduate_load="GR" fte)*(sum="");
run;
Data table contains grade, program, and incoming year of students. “Total” variable equals to 1 if the student has a grade; “ge75” is 1 if the grade is great than and equal to 75; and “ge80” is 1 if the grade is great than and equal to 80.
The following proc tabulate procedure gives a summary table of total number of counts of students with grade, students with grade great than and equal to 75 and 80.
proc tabulate missing f=6. noseps data=data;
class program progname category year level2;
var total ge75 ge80;
tables category,program*progname*level2,year*(total ge75 ge80) / rts=40 indent=3;
run;
PERCENTAGE STATISTICS
reppctn: report percentage (all rows and all columns add up to 100)
colpctn: column percentage (every column adds up to 100)
rowpctn: row percentage (every row adds up to 100)
A*pctsum: construct a custom denomination, both A and B are analysis variables, and B as in the is the denominator for calculating the percentage.
The following tabulate procedure calculates the percentage of greater and equal to 75 and 80. The outomatic SAS naming with ‘1111111’ of the percentage output reflects the number of class variables in the procedure.
proc tabulate missing f=6. noseps data=data out=dataout (drop=_type_ _table_ _page_ rename=(ge75_pctsum_1111111=ge75 ge80_pctsum_1111111=ge80));
class level1 faculty program progname category level2 year;
var total ge75 ge80;
tables category*level1*faculty*program*progname*level2*year, ge75*pctsum='ge75%'*f=8.2 ge80*pctsum='ge80%'*f=8.2 / rts=40 indent=3;
run;
Examples: with multiple class variables in row expression
Reppctn/Reppctsum
proc tabulate data = table1 ;
class Agegroup gender acadyear;
var heads ;
table agegroup="Age Group" all gender="Gender" all ,
(acadyear="" all)*(heads="Heads"*SUM="" heads="% Total"*reppctsum="" );
run;
Colpctn/Colpctsum — best for % sum for multiple class variables
proc tabulate data = table1 ;
class Agegroup gender acadyear;
var heads ;
table agegroup="Age Group" all gender="Gender" all ,
(acadyear="" all)*(heads="Heads"*SUM="" heads="% Total"*colpctsum="" );
run;
Rowpctn/Rowpctsum
proc tabulate data = table1 ;
class Agegroup gender acadyear;
var heads ;
table agegroup="Age Group" all gender="Gender" all ,
(acadyear="" all)*(heads="Heads"*SUM="" heads="% Total"*rowpctsum="" );
run;
pctsum – not working for the All column
proc tabulate data = table1 ;
class Agegroup gender acadyear;
var heads ;
table agegroup="Age Group" all gender="Gender" all ,
(acadyear="" all="All Year" )*(heads="Heads"*SUM="" heads="% Total"*pctsum < agegroup all gender all > ="" );
run;
Problem: myattrmap not reconginzed by the scatter statement though ‘attrid= myid’ doesn’t generate error.
data myattrmap;
retain id value linecolor fillcolor;
length linecolor $ 9 fillcolor $ 9;
input ID $ value $ linecolor $ fillcolor $;
datalines;
myid F blue blue
myid M red red;
run;
I came across an excel lookup function through the following link. https://www.extendoffice.com/documents/excel/2440-excel-vlookup-multiple-criteria.html
Then I found further explanation in the following forum.
This formula can be used to look up both numeric value and character value that match multiple conditions. COLUMN1=CONDITION1 will produce an array of 0s and 1s; 1 divided by the array will produce an array of %Div/0s and 1s; then the array will be further divided by an array of 0s and 1s (results of column2=condition2) and so on. The lookup value is set at 2 because 2 is greater than 1, and if lookup_value (2) is not found within the lookup_vector, LOOKUP returns the last value in that array which is less than 2. The formula then returns the value in the result column using the corresponding position found in the lookup columns.
In the previous blog, I presented how to compile statistic results by groups and by variable into a long table for each type of statistics analysis. All tables share three common field: group, test and var.
Here is the list of the table with the specific statistic that want to extract and compile into a summary table.
stat_tstat:
class = ‘1’: n, mean
class = ‘0’: n, mean
class = ‘Diff (1-2)’: mean,
stat_ttest:
variances = ‘Unequal’: tValue, Probt
PPOC SQL is the most convenient procedure to pick up these values under specific condition and combine them into one table.
proc sql;
create table stat1 as
select a.n as studyn, a.mean as studymean, b.n as controln, b.mean as controlmean, c.mean as diff, a.group, a.var, d.tValue, d.probt
from stat_tstat a
left join stat_tstat b
on a.group =b.group and a.var=b.var and b.class = '1'
left join stat_tstat c
on a.group =c.group and a.var=c.var and c.class = 'Diff (1-2)'
left join stat_ttest d
on a.group = d.group and a.var=d.var and d.variances = 'Unequal'
where a.class = '0';
quit;
The previous post goes through basic SAS statistics procedures, like Proc Means, Proc Ttest, and Proc Freq. In this post, the focus is to establish a good naming convention for the statistical outputs. For each loop of PROC TTEST or PROC FREQ, the statistics are saved in separate tables by pairing group and type of statistics. When stacking the test results, it is important to identify the type of statistics, the variable that the statistics is based on and the pairing group. In the following macro, &in. is the group identifier, &var. is the variable identified, and &stat is the statistics procedure identifier.
%macro stat (in =, var=, stat=);
data &in.&stat.&var.;
set &in.&stat.&var.;
length group $2 test $20 var $20;
group = substr("&in.", 1, 2);
test = "&stat.";
var = "&var.";
rename &var. = response;
run;
proc append base=stat_&stat. data = &in.&stat._&var.;
run;
%mend stat;
proc datasets library= work noprint;
delete stat_prop;
run;
%stat ( in =g1, var= isretainYr1,stat=prop);
%stat ( in =g1, var= isretainYr2,stat=prop);
%stat ( in =g1, var= isretainYr3,stat=prop);
%stat ( in =g1, var= isretainYr4,stat=prop);
%stat ( in =g2, var= isretainYr1,stat=prop);
%stat ( in =g2, var= isretainYr2,stat=prop);
%stat ( in =g2, var= isretainYr3,stat=prop);
%stat ( in =g2, var= isretainYr4,stat=prop);
