* FINAL PROJECT BY: PINGMEI LU LIBING SHI ; * First introduce several methods to exchange data between excel and sas; 1.use import command; 2.use sas dde(Dynamic data exchange). **************************************************************************** ; filename job_1 'a:\project\jobclass.dat'; data jobclass;set work.data1; file job_1 print notitles ; put @2 gender region occupat ; return; run; data job_1; infile 'a:\project\jobclass.dat'; input @2 gender region occupat ; proc print data=job_1; run; ***********************************************************************; filename data1 dde 'excel|data1!r2c1:r124c3' ; data jobclass; infile data1; input gender region occupat; proc print; run; ***********************************************************************; filename data1 dde 'excel|sheet1!r2c1:r124c3' ; data _null_; infile 'a:\project\jobclass.dat' ; input @2 gender region occupat ; file data1; put gender region occupat ; run; filename data1 dde 'excel|sheet1!r2c4:r124c5' ; data _null_; infile cards ; input gender region occupat @@; file data1; put gender region occupat ; cards; 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 1 1 2 1 1 2 1 1 2 1 1 2 1 1 2 1 1 2 1 1 3 1 1 3 1 1 3 1 1 3 1 1 3 1 1 3 1 1 3 1 2 1 1 2 1 1 2 1 1 2 2 1 2 2 1 2 2 1 2 2 1 2 2 1 2 2 1 2 3 1 2 3 1 2 4 1 2 4 1 2 4 1 2 4 1 2 4 1 2 4 1 3 1 1 3 1 1 3 1 1 3 1 1 3 1 1 3 2 1 3 2 1 3 2 1 3 2 1 3 2 1 3 2 1 3 2 1 3 3 1 3 3 1 3 3 1 3 3 1 3 4 1 3 4 1 3 4 1 3 4 1 3 4 1 4 1 1 4 3 2 1 1 2 1 1 2 1 1 2 1 1 2 1 1 2 1 1 2 1 1 2 1 2 2 1 2 2 1 2 2 1 2 2 1 2 2 1 3 2 1 3 2 1 3 2 1 4 2 1 4 2 1 4 2 1 4 2 1 4 2 1 4 2 2 1 2 2 3 2 2 3 2 2 3 2 2 3 2 2 3 2 2 4 2 2 4 2 2 4 2 2 4 2 2 4 2 3 1 2 3 1 2 3 1 2 3 1 2 3 1 2 3 2 2 3 2 2 3 2 2 3 2 2 3 2 2 3 2 2 3 2 2 3 3 2 3 3 2 3 4 2 3 4 2 3 4 2 4 1 2 4 1 2 4 1 2 4 1 2 4 1 2 4 2 2 4 2 2 4 2 2 4 3 2 4 3 2 4 3 2 4 3 2 4 4 ; run; ******************************************************; ******************************************************; data jobclass; infile 'a:\project\job.dat'; input gender region occupat @@; data jobclass; input gender region occupat @@; cards; 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 1 1 2 1 1 2 1 1 2 1 1 2 1 1 2 1 1 2 1 1 3 1 1 3 1 1 3 1 1 3 1 1 3 1 1 3 1 1 3 1 2 1 1 2 1 1 2 1 1 2 2 1 2 2 1 2 2 1 2 2 1 2 2 1 2 2 1 2 3 1 2 3 1 2 4 1 2 4 1 2 4 1 2 4 1 2 4 1 2 4 1 3 1 1 3 1 1 3 1 1 3 1 1 3 1 1 3 2 1 3 2 1 3 2 1 3 2 1 3 2 1 3 2 1 3 2 1 3 3 1 3 3 1 3 3 1 3 3 1 3 4 1 3 4 1 3 4 1 3 4 1 3 4 1 4 1 1 4 3 2 1 1 2 1 1 2 1 1 2 1 1 2 1 1 2 1 1 2 1 1 2 1 2 2 1 2 2 1 2 2 1 2 2 1 2 2 1 3 2 1 3 2 1 3 2 1 4 2 1 4 2 1 4 2 1 4 2 1 4 2 1 4 2 2 1 2 2 3 2 2 3 2 2 3 2 2 3 2 2 3 2 2 4 2 2 4 2 2 4 2 2 4 2 2 4 2 3 1 2 3 1 2 3 1 2 3 1 2 3 1 2 3 2 2 3 2 2 3 2 2 3 2 2 3 2 2 3 2 2 3 2 2 3 3 2 3 3 2 3 4 2 3 4 2 3 4 2 4 1 2 4 1 2 4 1 2 4 1 2 4 1 2 4 2 2 4 2 2 4 2 2 4 3 2 4 3 2 4 3 2 4 3 2 4 4 ; run; proc format ; value gendfmt 1='Female' 2='Male' other='*** Data Entry Error ***'; value occupfmt 1='Technical' 2='Manager/Supervisor' 3='Clerical' 4='Administrative' other='*** Data Entry Error ***'; value regfmt 1='North' 2='South' 3='East' 4='West' other='*** Data Entry Error ***'; PROC PRINT; FORMAT GENDER GENDFMT. OCCUPAT OCCUPFMT. REGION REGFMT.; run; *******************************************************************************; * In this table shows frequency counts for female and males within each of four * job classes. The table also shows the percentage that each frequency cout: * (1) the total women and men in that job class(row percentage) * (2) the total for that gender in all job classes(column percentage) * (3) the total for all employees. *******************************************************************************; options nodate pageno=1 linesize=132 pagesize=80 ; *pageno=1: page number of output begins from 1; proc tabulate data=jobclass format=8.2; class gender occupat; table (occupat='Job Class' all='All Jobs') *(n='Number of employees'*f=9. pctn='Percent of row total' pctn='Percent of column total' pctn='Percent of total'), gender='Gender' all='All Employees'/ rts=50 ; format gender gendfmt. occupat occupfmt.; title 'Gender Distribution'; title2 'within Job Classes'; run; proc tabulate data=jobclass format=8.2 noseps; class gender occupat; table (occupat all='All Jobs') * (n='Number of employees'*f=9. pctn='Percent of row total' pctn='Percent of column total' pctn='Percent of total'), gender='Gender' all='All Employees'/ rts=38 box=' JOB CLASS' indent=10 misstext='0'; format gender gendfmt. occupat occupfmt.; title 'Gender Distribution'; title2 'within Job Classes'; run; ********************************************************************************* * compare with proc freq: * The table statement in 'proc freq' is much simpler than the table statement * in 'proc tabulate'. 'proc tabulate' automatically calculates totals as well * as row ,column,and total percentages. *********************************************************************************; options nodate pageno=1 linesize=80 pagesize=60 ; proc freq data=jobclass; tables occupat*gender; label occupat='Job class'; format gender gendfmt. occupat occupfmt.; title 'Gender Distribution'; title2 'within Job Classes'; run; ******************************************************************************* * gender: is a nest variable. * This table shows for each job class the frequency and percentages for * each gender within each region *******************************************************************************; options nodate nonumber linesize=132 pagesize=60 ; proc tabulate data=jobclass format=5. noseps; class gender region occupat; table occupat=' ' all='All Employees', region='Region'*gender=' ' *(n='Count' pctn='%'*f=7.2) / rts=20 misstext='0' box='Job Class'; format gender gendfmt. occupat occupfmt. region regfmt.; title 'Regional Gender Distribution'; title2 'among Job Classes'; run; ************************************************************************* * syntax: * * TABLE < row_expression,> column_expression * ; * * Use value of the page_expression to present table on separate pages. * In our program the page_variable is gender *************************************************************************; options nodate nonumber linesize=120 pagesize=100 ; proc tabulate data=jobclass format=5.; class gender region occupat; table gender='Gender: ' all='All Employees', occupat=' ' all='All Jobs', (region='Region' all='All Regions') *(n='Count' pctn='% of Category'*f=8.2) / rts=20 box=_page_ condense; * box=_page_ : cause the page dimension text to appear in the box; format gender gendfmt. occupat occupfmt. region regfmt.; title 'Summarization of Jobs by Region'; title2 'for Each Gender and for All Employees'; run; **************************************************************** * syntax: * BY variable-1 * <... VARIABLE-N>; * Using a BY statement is similar to using a page dimension. * Sorting is unnecessary when using BY statement; ****************************************************************; options nodate pageno=1 linesize=160 pagesize=80 ; proc tabulate data=jobclass format=5.; by descending gender; class region occupat; format gender gendfmt. occupat occupfmt. region regfmt.; table occupat=' ' all='All Jobs', (region='Region' all='All Regions') *(n='Count' pctn='%'*f=7.2) / rts=20; title 'Summarization of Jobs by Region'; run; options nodate pageno=1 linesize=160 pagesize=100 nobyline; **********************************************************************; * nobyline: suppress the default byline; * We use '#byline' in title to print out the value of the by_variable **********************************************************************; proc tabulate data=jobclass format=5.; by gender; class region occupat; format gender gendfmt. occupat occupfmt. region regfmt.; table occupat=' ' all='All Jobs', (region='Region' all='All Regions') *(n='Count' pctn='%'*f=7.2) / rts=20 ; title 'Summarization of Jobs by Region'; title3 'Data for #byline'; run;