1 / 34

NEW FEATURES APPLIED IN INSTITUTIONAL RESEARCH

NEW FEATURES APPLIED IN INSTITUTIONAL RESEARCH. Dr. Robert W. Zhang Associate Director of Institutional Research Bowling Green State University Bowling Green, Ohio 43403. Contents. SAS version 8 window environment Import external files to SAS data set

edith
Download Presentation

NEW FEATURES APPLIED IN INSTITUTIONAL RESEARCH

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. NEW FEATURES APPLIED ININSTITUTIONAL RESEARCH Dr. Robert W. Zhang Associate Director of Institutional Research Bowling Green State University Bowling Green, Ohio 43403

  2. Contents • SAS version 8 window environment • Import external files to SAS data set • SAS macro • Tabulation, Chart, and Map procedures • Output Delivery System (ODS) • ODS used in IR routine projects • Export SAS data set to external files

  3. SAS Import Wizard(e.g. To import a database table from MS Access)FileImport Data Select Microsoft Access 2000 Table from put-down menu and Click Next  Key in “c:\data\enroll.mdb” after “Database:” or click Browse… to select database table then Click OK Select enr993 in “What table do you want to import?” window Click Options Change length of Memo fields as you desired and Click OK  Click NextChoose the SAS destination: leave Work in ‘Library:’ window and key in enr993in ‘Member:’ window Click Next Key in c:\SASProg\table.sas ClickFinish.

  4. Import database tablePROCIMPORT OUT= WORK.dataname DATATABLE= “tablename” DBMS=ACCESS2000 REPLACE; DATABASE=“c:\dir\database.mdb";RUN;

  5. Import spreadsheetPROCIMPORT OUT= WORK.dataname DATAFILE= "C:\dir\filename.xls" DBMS=EXCEL2000 REPLACE; RANGE="Sheet1$"; GETNAMES=YES;RUN;

  6. Import regular delimited filePROCIMPORT out=work.dataname datafile=‘c:/exclamation.txt' dbms=dlm replace; delimiter='!'; getnames=no;

  7. Import TAB delimited filePROCIMPORT OUT= WORK.TAB DATAFILE= "a:\tabfile.txt" DBMS=TAB REPLACE; GETNAMES=NO; DATAROW=1;

  8. Use %LET to define macro variables%LET RPTYEAR = 2001;%LET RPTTERM = Fall;%LET SUBTITLE = &RPTTERM &RPTYEAR;

  9. Macro language elements used often in IR projects 1. Macro functions %EVAL evaluates arithmetic and logical expressions using integer arithmetic. %LENGTH returns the length of a string. %SUBSTR produces a substring of a characater string. e.g. %LET YR2=%EVAL(2001+1); &YR2=2002 %LET YRLENGTH=%LENGTH(&YR2); &YRLENGTH=4 %LET FLNAME=ENR%SUBSTR(2001,3,2)3; &FLNAME=ENR013

  10. Macro language elements used often in IR projects 2. Macro statements %IF-%THEN-%ELSE conditionally processes a portion of a macro %DO begins a %DO group %DO I=1 %TO N executes statements repetitively, based on the value of an index variable %END ends a %DO group e.g.%if &rptterm = Spring %then%let term=1; %else %if &rptterm = Summer %then%let term=2; %else %let term=3; %DO I=1%TO 5; text and macro language statements %END;

  11. %MACRO statements%MACROmacro-name;macro definition data statement proc procedure%MEND macro-name;%macro-name;

  12. %MACRO statements e.g.%LET RPTTERM = Fall;%MACRO ACADYR(RPTYR);%GLOBAL ACADYR BEGYR ENDYR TERM; %LET ENDYR=&RPTYR; %LET BEGYR=%EVAL(&ENDYR-4);%LET YEAR2=%EVAL(&RPTYR+1);%LET ACADYR=%SUBSTR(&RPTYR,3,2)%SUBSTR(&YEAR2,3,2);%IF &RPTTERM=Spring %then%let TERM=1;%ELSE%IF &RPTTERM=Summer %THEN%LET TERM=2;%ELSE%LET TERM=3;%MEND ACADYR;%ACADYR(2001);Macro variable’s value returned:&RPTYR=2001&ENDYR=2001%EVAL(&ENDYR-4)=1997&BEGYR=1997%EVAL(&RPTYR+1)=2002&YEAR2=2002%SUBSTR(&RPTYR,3,2)=01%SUBSTR(&YEAR2,3,2)=02 &ACADYR=0102&RPTTERM=Fall &TERM=3

  13. Automatic Macro VariablesSYSDAY SYSDATE SYSTIME%put today=&sysday &sysdate;today=Sunday 02JUN02%put now=&systime;now=14:20

  14. Use Macro to Import multipleworksheets%macro readfl(j);%DO i=1%TO &j; PROC IMPORT OUT= WORK.place&i DATAFILE= "E:\DATA\Placement.xls" DBMS=EXCEL2000 REPLACE; RANGE="Sheet&i$"; GETNAMES=YES;%IF &i>1%THEN%DO; PROC APPEND BASE=place1 DATA=place&i force;%END;%end;%mend readfl;%readfl(3);run;

  15. Tabulation ProcedureProcTabulateDATA=tab format=6.;title'Student Age Distribution by Gender'; keylabel all='Total' n=' ';class gender age;table gender=' ' all, age all/rts=10;run;

  16. GChart Procedureprocgchart;title'Student SAT Score by Gender';vbar3d sat/subgroup=gender cframe=white;label n=' ' frequency=' ';run;

  17. Map Proceduregoptionsgunit=pct bordercback=white colors=(cyan yellow red blue lime green lipk)ctext=black ftext=swiss htitle=6htext=3; title1'OCAIR Membership Distribution ';footnote1 justify=right 'AS OF '&sysdate;procgmap map=maps.us data =clasdata.ocair;id state;choro count / coutline=gray;run;

  18. Use macro in tabulation%macro tabulat(colvar); Proc Tabulate data=tab format=6.; title 'Student' &colvar 'Distribution by Gender'; keylabel all='Total' n=' '; class gender &colvar; table gender=' ' all, &colvar all/rts=10; run;%mend tabulat;%tabulat(Age);%tabulat(SAT);%tabulat(GPA);

  19. Use macro in graph%macro gchart(colvar);proc gchart; title 'Student' &colvar 'by Gender'; vbar3d &colvar/subgroup=gender cframe=white; label n=' ' frequency=' ';run;%mend gchart;%gchart(Age);%gchart(SAT);%gchart(GPA);

  20. Output Delivery System (ODS)ODS Listing DestinationODS HTML DestinationODS Printer Destination

  21. ODS Statementsodslisting; odsprinter; procunivariate; ProcTabulate;ProcTabulate; proc gchart; odslisting close; odsprinterclose;odshtml body='e:\web\odshtml-body.htm'ProcReport;proc gmap;odsprinterclose;

  22. ODS used in IR routine projectsA. Fact BookB.CUPAC. AAUP

  23. DS used in Fact Bookodslistingclose;%macro cr_table(gm,rvar,ttl); ods html body="&rvar..htm" path=“c:\STUDIES\FACTBOOK\"; TITLE1 &ttl; PROC TABULATE DATA = ENROL FORMAT = COMMA6. NOSEPS MISSING ORDER=FORMATTED; FORMAT YRTERM $YRTERM. AGE AGE_RG. GENDER $GENDER. RACE $FBRACE.; KEYLABEL ALL= 'TOTAL' pctn='%'; CLASS &rvar YRTERM; TABLE &rvar=' ' ALL, YRTERM=' '*(N PCTN<&rvar all>*F=5.1) / RTS=45 ROW=FLOAT;%if &gm=g %then%do; proc gchart DATA = ENROL; vbar3d yrterm/subgroup=&rvar cframe=PINK; label n=' ' frequency=' ' yrterm=' ' &rvar=' '; FORMAT GENDER $GENDER. RACE $FBRACE. YRTERM $YRTERM. AGE AGE_RG.;%end;

  24. ODS used in Fact Book(Continued)%else%do; goptions reset=global gunit=pct border cback=white colors=(lime cyan yellow red green orange blue pink brown violet purple olive) ctext=black ftext=swiss htitle=6 htext=3; TITLE1 &ttl;proc gmap map=maps.us data=enrct; id state; choro count / nolegend discrete coutline=gray annotate=mlabel;%end; Footnote1 "Sources: Office of Institutional Research"; Footnote2 "Last Updated: &sysdate"; RUN;%mend cr_table;%cr_table(g,GENDER,'HEADCOUNT ENROLLMENT BY GENDER');%cr_table(g,RACE,'HEADCOUNT ENROLLMENT BY ETHNICITY');%cr_table(g,AGE,'HEADCOUNT ENROLLMENT BY AGE');%cr_table(m,state,'HEADCOUNT ENROLLMENT BY STATE');odshtmlclose;odslisting;RUN;

  25. ODS used in CUPA surveyODSLISTINGclose;%macroHTML_TB; ods html body="CUPA&acyr..xls" path=“c:\studies\cupa\"; PROC TABULATE DATA = FAC FORMAT = COMMA10. NOSEPS MISSING ORDER=FORMATTED; FORMAT NEWASST NEWASST. JRANK $RANK. dept $CUPACIP.; KEYLABEL N='NUM' ALL= 'ALL'; CLASS dept JRANK NEWASST; VAR SALARY; TABLE dept=' ' *(JRANK=' ' ALL='ALL RANKS' NEWASST=' ') ALL='UNIV TOTAL'*(JRANK=' ' ALL='ALL RANKS' NEWASST=' '), SALARY=' '*(MIN MAX MEAN) N*F=COMMA5. /RTS = 47 ROW=FLOAT; %mend HTML_TB;%HTML_TB;RUN;ODSHTMLCLOSE;ODSLISTING;

  26. ODS used in AAUP survey%macro cr_html(F,SECT,ttl);ODS LISTING close;ods html body="AAUP&acyr.SEC&SECT..xls" path=“C:\STUDIES\AAUP\"; TITLE5 "&TTL";%IF &F=A %THEN%LET FILENM = &CURR;%IF &F=B %THEN%LET FILENM = FIN;PROC TABULATE DATA = &FILENM FORMAT = COMMA6. NOSEPS MISSING ORDER=FORMATTED;%if &SECT = 1%then%do; FORMAT JRANK $RANK. SEX $GENDER. TENSTAT $TENSTAT. JAPPTTM $APPTTM.; KEYLABEL N='N' ALL= 'TOTAL' SUM=' '; CLASS JRANK SEX TENSTAT JAPPTTM TENST; VAR SALARY ; TABLE (JAPPTTM=' ' ALL)*(JRANK=' ' ALL), SEX=' '*(ALL SALARY='TOTAL SALARIES'*F=14. TENST=' ' TENSTAT=' ') / RTS = 30 ROW=FLOAT;%end;%else%if &SECT = 4%then%do; FORMAT JRANK $RANKB. JAPPTTM $APPTTM. SALARY SALDIST.; KEYLABEL N=' ' ALL= 'TOTAL'; CLASS JRANK JAPPTTM SALARY; TABLE SALARY=' ' ALL,JAPPTTM=' '*JRANK=' '*f=6. / RTS = 30 ROW=FLOAT;%end;

  27. ODS used in AAUP survey(Continued)%else%do; FORMAT RANK $RANK. JAPPTTM $APPTTM. ; KEYLABEL N='NUMBER' ALL= 'TOTAL' SUM='SALARY'; CLASS RANK JAPPTTM; VAR SALARY LASTSLRY SALCHG; TABLE JAPPTTM=''*(RANK=' ' ALL), N*F=6. (SALARY='THIS YEAR' LASTSLRY='LAST YEAR')*f=COMMA10. SALCHG=' '*PCTSUM<LASTSLRY>='% INCREASE'*F=8.2 / RTS = 40 ROW=FLOAT;%end;run;%mend cr_html;*-----------------------------------------------------------;%cr_html(A,1,'SECTION I: NUMBER, TOTAL SALARIES, AND TENURE STATUS');%cr_html(B,3,'SECTION III: SALARIES AND PERCENTAGE INCREASE');%cr_html(A,4,'SECTION IV: DISTRIBUTION OF FULL-TIME FACULTY');RUN;ODSHTMLCLOSE;ODSLISTING;

  28. SAS Export WizardFile Export Data  In ‘Choose source SAS data set’, selectClasdatafromLibrary: put-down menu and selectEnr973from Member: put-down menu.  ClickNext.Select Microsoft Access 2000 Tablefrom Data Source: put-down menu  Click NextKey in “c:\data\enroll.mdb” after “Database:” or clickBrowse…to select “c:\data\enroll.mdb” OK  Key inEnr973 in “What table do you want to export?” window Click Next  if you want to save this wizard process as a SAS program for future use, then Key in the SAS program name. Otherwise, leave the window blank. click Finish

  29. Export dataset to database tablePROCEXPORT DATA= WORK.DATANAME OUTTABLE= "TABLENAME" DBMS=ACCESS2000 REPLACE; DATABASE="C:\DIRECTORY\DATABASENAME.mdb"; RUN;

  30. Export dataset to spreadsheetPROCEXPORT DATA= WORK.DATANAME OUTFILE= "C:\DIRECTORY\FILENAME.xls" DBMS=EXCEL2000 REPLACE;RUN;

  31. Export dataset to regular delimited text filePROCEXPORT DATA= WORK.dataname OUTFILE= "c:\data\filename.txt" DBMS=DLM REPLACE; DELIMITER='!'; RUN;

  32. Export dataset to TAB delimited text filePROCEXPORT DATA= WORK.dataname OUTFILE= "c:\DATA\filename.txt" DBMS=TAB REPLACE;RUN;

  33. RENFERENCESAS Institute Inc., New Features in Version 8 of the SAS® System Course Notes, Cary,NC: SASInstitute Inc., 2000. 456 pages.SAS Institute Inc., SAS® Macro Language: Reference, Version 8,Cary,NC: SASInstitute Inc., 1999. 310 pages.SAS Institute Inc., The Complete Guide to the SAS® Output Delivery System, Version 8,Cary,NC: SASInstitute Inc., 1999. 310 pages.SAS Institute Inc., SAS/GRAPH® Software: Reference, Version 8,Cary,NC:SASInstitute Inc., 1999. 1240 pagesSAS Institute Inc., SAS® Guide to TABULATE Processing, Second Edition,Cary,NC: SASInstitute Inc., 1990. 208 pages

  34. NEW FEATURES APPLIED ININSTITUTIONAL RESEARCHEnd Dr. Robert W. Zhang Associate Director of Institutional Research Bowling Green State University 301 McFall Center Bowling Green, OH 43403 (419)372-6014 (Phone) (419)372-7878 (Fax) Rzhang@bgnet.bgsu.edu

More Related