The latest way: Run sas and the next moment you see a nice graph in an excel sheet (though it still requires some excel work to get the first excel graph done)




Скачать 43.28 Kb.
НазваниеThe latest way: Run sas and the next moment you see a nice graph in an excel sheet (though it still requires some excel work to get the first excel graph done)
Дата конвертации10.02.2016
Размер43.28 Kb.
ТипДокументы
источникhttp://www.estat.us/sas/ExcelGraph_SAS_DDE.doc
Creating an Excel graph using SAS DDE (dynamic data exchange)

By Kaz Uekawa


Description:


I describe a way to create graphs, using SAS and Excel. My goal is to have a system that:

  1. requires a minimal number of files and software (I just need one SAS syntax file and one excel sheet.)

  2. allows quick editing of graphs (I can update the graph by rerunning a SAS syntax).

  3. make use of the fact that Excel’s graph is easier to use than SAS-graph. (It takes too much patience to create photo-ready graphs in SAS.)


The system that I describe lets SAS do statistics and throw the values into an Excel sheet via DDE (dynamic data exchange). Based on the values, as well as the texts that came from SAS, you create a graph in excel. Advantages over the use of PROC EXPORT (that creates an excel version of data) will be noted.



  • Old way: Run SAS, Print out Results, and manually create excel graph.

  • Better way: Run SAS, use ODS to produce result data sets, manipulate the data sets, import them as excel data sets and edit the excel graph.

  • The latest way: Run SAS and the next moment you see a nice graph in an excel sheet (though it still requires some excel work to get the first excel graph done).


Files to download:

  • This document “Memo Creating an Excel graph using SAS DDE.doc

  • Excel sheet “createExcel_example.xls” to be placed at C:temp\ . Or you can have a blank excel sheet of your own.

  • SAS program CreateExcel1.1.sas (Also in Appendix)


1. Example Data Set

Each line of observation is reporting results of a logistic regression model. Desc is the description of the outcome variable. Collegialtime is a subsample indicator. CoefEstimate is a coefficient of an independent variable. CI_onetail and CI_two tails are confidence interval information of the coefficient.


/*Note that first half of observations are Group A

and the other half is Group B. I create two graphs for each group*/


data temp;

input group $ 1 desc $ 3-33 collegialtime coefEstimate CI_twotails CI_onetail ;

cards;

A SPOKEN TO PARENTS ABT STUDNT P 0 1.114173835 0.368234289 0.43902259

A SPOKEN TO PARENTS ABT STUDNT P 1 0.860347099 0.120992552 0.144187211

A SPOKEN TO PARENTS ABT STUDNT P 2 0.706472479 0.089291097 0.106404031





B SPOKEN TO PARENTS ABT STUDNT P 0 1.114173835 0.368234289 0.43902259

B SPOKEN TO PARENTS ABT STUDNT P 1 0.860347099 0.120992552 0.144187211

B SPOKEN TO PARENTS ABT STUDNT P 2 0.706472479 0.089291097 0.106404031

B SPOKEN TO PARENTS ABT STUDNT P 3 0.713863537 0.185214602 0.220762238

0.394908983



;

run;


2. POPULATING EXCEL SHEET with Values and Texts

%macro excel (variable=,data=, group=,sheet1=,tabletitle=);

/*populating with values that are from SAS data sets*/

FILENAME ddedata DDE

"excel|C:\TEMP\[createExcel_example.xls]&Sheet1.!r2c1:r30c9" notab;

FILENAME ddecmds DDE "excel|system";

data _null_;

set &data;

format desc $char30.;

where group="&group";

file ddedata;

put &variable ;

run;


/*adding texts (In this case titles) to the excel sheet*/

FILENAME ddedata DDE "excel|C:\TEMP\[createExcel_example.xls]&Sheet1.!r2c9" notab;

FILENAME ddecmds DDE "excel|system";

data _null_;

file ddedata;

a="&tabletitle" ;

put a $110. ;

run;

%mend excel;


/*First I use observations from Group A*/

%excel (data=temp,

group=A,

tabletitle=Figure 4 The Effect of Selected Independent Variables on Student-Teacher Cohesion-- By Collegial Cohesion,

sheet1=Sheet 1,

variable=

desc '09'x

collegialtime '09'x

coefEstimate '09'x

CI_twotails '09'x CI_onetail);


When the macro is run, the excel sheet will look like this.





Based on this data, you will manually create an excel graph like the following:





Here just one excel tutorial: The title of the graph can be connected to a cell of a graph (above, See CELL 2I). You can do this buy clicking on the graph title first and then to the excel’s small window (where they take values) you type “=.” Next you click on the cell that has the title of the graph (Above example, CELL I2). This is nice because SAS can now throw a value to that cell to change the title of the graph.


This graph will be updated if you run SAS again. You can now change the graph from SAS now. Imagine you found a mistake in your statistical analysis or want to change the title of the graph. Now it is easy to update graphs.

CREATING a SECOND GRAPH


You want to create a second graph. Create a new tab by copying the tab (In the above picture I already created the second tab “Sheet 1 (2)”. The macro below will populate this new tab with data from group B observations (the latter half of the data). This time you don’t need to create a new graph since the basic structure of the graph will be carried over from the first tab.


/*Second I use observations from Group B*/

%excel (data=temp,

group=B,

tabletitle=Figure 5 The Effects of Selected Independent Variables on Student-Teacher Cohesion--By Collegial Cohesion,

sheet1=Sheet 1 (2),

variable=

desc '09'x

collegialtime '09'x

coefEstimate '09'x

CI_twotails '09'x

CI_onetail

);


This time you will get the following graph without manually creating a new excel graph!




You can embed these excel graphs in word documents with LINKS. In this way, you have a way to control how the graph should look like in a word document THROUGH SAS via Excel. Now SAS, Excel, and WORD are all linked up.

APPENDIX

The entire program


/*Note that first half of observations are Group A

and the other half is Group B. I create two graphs for each group*/

data temp;

input group $ 1 desc $ 3-33 collegialtime coefEstimate CI_twotails CI_onetail ;

cards;

A SPOKEN TO PARENTS ABT STUDNT P 0 1.114173835 0.368234289 0.43902259

A SPOKEN TO PARENTS ABT STUDNT P 1 0.860347099 0.120992552 0.144187211

A SPOKEN TO PARENTS ABT STUDNT P 2 0.706472479 0.089291097 0.106404031

A SPOKEN TO PARENTS ABT STUDNT P 3 0.713863537 0.185214602 0.220762238

A SPOKEN TO PARENTS ABT STUDNT P 4 0.683423279 0.220576291 0.262953853

A SPOKEN TO PARENTS ABOUT STUDEN 0 0.754008715 0.404580521 0.482362387

A SPOKEN TO PARENTS ABOUT STUDEN 1 0.57082434 0.138580051 0.165146954

A SPOKEN TO PARENTS ABOUT STUDEN 2 0.30814458 0.100333051 0.119562443

A SPOKEN TO PARENTS ABOUT STUDEN 3 0.407208269 0.204519114 0.243773819

A SPOKEN TO PARENTS ABOUT STUDEN 4 0.336933718 0.239274055 0.28524714

A SPOKE TO STUDNT^S COUNSELOR-PE 0 0.379213733 0.430480886 0.513235292

A SPOKE TO STUDNT^S COUNSELOR-PE 1 0.010697354 0.154792569 0.184467492

A SPOKE TO STUDNT^S COUNSELOR-PE 2 0.024430483 0.109489782 0.130474075

A SPOKE TO STUDNT^S COUNSELOR-PE 3 0.1345304 0.22442609 0.267501935

A SPOKE TO STUDNT^S COUNSELOR-PE 4 0.229176809 0.255753725 0.304894105

A SPOKE TO STUDNT^S COUNSELOR RE 0 0.84466878 0.616938583 0.735542779

A SPOKE TO STUDNT^S COUNSELOR RE 1 -0.125577435 0.198815334 0.236930222

A SPOKE TO STUDNT^S COUNSELOR RE 2 -0.170307396 0.143277258 0.170737336

A SPOKE TO STUDNT^S COUNSELOR RE 3 -0.005236505 0.278520123 0.331980435

A SPOKE TO STUDNT^S COUNSELOR RE 4 0.075311893 0.331258596 0.394908983

B SPOKEN TO PARENTS ABT STUDNT P 0 1.114173835 0.368234289 0.43902259

B SPOKEN TO PARENTS ABT STUDNT P 1 0.860347099 0.120992552 0.144187211

B SPOKEN TO PARENTS ABT STUDNT P 2 0.706472479 0.089291097 0.106404031

B SPOKEN TO PARENTS ABT STUDNT P 3 0.713863537 0.185214602 0.220762238

B SPOKEN TO PARENTS ABT STUDNT P 4 0.683423279 0.220576291 0.262953853

B SPOKEN TO PARENTS ABOUT STUDEN 0 0.754008715 0.404580521 0.482362387

B SPOKEN TO PARENTS ABOUT STUDEN 1 0.57082434 0.138580051 0.165146954

B SPOKEN TO PARENTS ABOUT STUDEN 2 0.30814458 0.100333051 0.119562443

B SPOKEN TO PARENTS ABOUT STUDEN 3 0.407208269 0.204519114 0.243773819

B SPOKEN TO PARENTS ABOUT STUDEN 4 0.336933718 0.239274055 0.28524714

B SPOKE TO STUDNT^S COUNSELOR-PE 0 0.379213733 0.430480886 0.513235292

B SPOKE TO STUDNT^S COUNSELOR-PE 1 0.010697354 0.154792569 0.184467492

B SPOKE TO STUDNT^S COUNSELOR-PE 2 0.024430483 0.109489782 0.130474075

B SPOKE TO STUDNT^S COUNSELOR-PE 3 0.1345304 0.22442609 0.267501935

B SPOKE TO STUDNT^S COUNSELOR-PE 4 0.229176809 0.255753725 0.304894105

B SPOKE TO STUDNT^S COUNSELOR RE 0 0.84466878 0.616938583 0.735542779

B SPOKE TO STUDNT^S COUNSELOR RE 1 -0.125577435 0.198815334 0.236930222

B SPOKE TO STUDNT^S COUNSELOR RE 2 -0.170307396 0.143277258 0.170737336

B SPOKE TO STUDNT^S COUNSELOR RE 3 -0.005236505 0.278520123 0.331980435

B SPOKE TO STUDNT^S COUNSELOR RE 4 0.075311893 0.331258596 0.394908983

;

run;


%macro excel (variable=,data=, group=,sheet1=,tabletitle=);

/*populating with values that are from SAS data sets*/

FILENAME ddedata DDE

"excel|C:\TEMP\[createExcel_example.xls]&Sheet1.!r2c1:r30c9" notab;

FILENAME ddecmds DDE "excel|system";

data _null_;

set &data;

format desc $char30.;

where group="&group";

file ddedata;

put &variable ;

run;


/*adding texts (In this case titles) to the excel sheet*/

FILENAME ddedata DDE "excel|C:\TEMP\[createExcel_example.xls]&Sheet1.!r2c9" notab;

FILENAME ddecmds DDE "excel|system";

data _null_;

file ddedata;

a="&tabletitle" ;

put a $110. ;

run;


%mend excel;

/*First I use observations from Group A*/

%excel (data=temp,

group=A,

tabletitle=Figure 4 The Effect of Selected Independent Variables on Student-Teacher Cohesion-- By Collegial Cohesion,

sheet1=Sheet 1,

variable=

desc '09'x

collegialtime '09'x

coefEstimate '09'x

CI_twotails '09'x CI_onetail);


/*Second I use observations from Group B*/

%excel (data=temp,

group=B,

tabletitle=Figure 5 The Effects of Selected Independent Variaables on Student-Teacher Cohesion--By Collegial Cohesion,

sheet1=Sheet 1 (2),

variable=

desc '09'x

collegialtime '09'x

coefEstimate '09'x

CI_twotails '09'x

CI_onetail

);

Похожие:

The latest way: Run sas and the next moment you see a nice graph in an excel sheet (though it still requires some excel work to get the first excel graph done) iconImporting an Excel Worksheet into sas

The latest way: Run sas and the next moment you see a nice graph in an excel sheet (though it still requires some excel work to get the first excel graph done) iconExcel программасын жүктеу үшін тапсырмалар тақтасындағы Іске қосу (Пуск) батырмасын таңдап, Программалар қатарындағы Microsoft Excel жолында тышқанды шертеміз
Пуск батырмасын таңдап, Программалар қатарындағы Microsoft Excel жолында тышқанды шертеміз. Егер жұмыс үстелінде Excel программасының...
The latest way: Run sas and the next moment you see a nice graph in an excel sheet (though it still requires some excel work to get the first excel graph done) iconДӘріс №16 microsoft excel электрондық кестесін қҰру жоспар
Ол Excel программасын іске қосқаннан кейін компьютер экранында терезе шығады (программаны баптауа байланысты өзгеруі ықтимал.)
The latest way: Run sas and the next moment you see a nice graph in an excel sheet (though it still requires some excel work to get the first excel graph done) iconРеферат Транспортные задачи в Excel
Решение классической сбалансированной транспортной задачи в Excel
The latest way: Run sas and the next moment you see a nice graph in an excel sheet (though it still requires some excel work to get the first excel graph done) iconThe Excel Interface t he names of the various parts of the Excel interface are shown above. Standard Toolbar

The latest way: Run sas and the next moment you see a nice graph in an excel sheet (though it still requires some excel work to get the first excel graph done) iconMövzu 1: Microsoft Excel cədvəl redaktoru haqqında ümumi məlumat
Пуск –Программы Microsoft Offise Microsoft Excel 2007 əmrlərini ardıcıl seçirik
The latest way: Run sas and the next moment you see a nice graph in an excel sheet (though it still requires some excel work to get the first excel graph done) iconExcel электрондық кесте құралдарымен мәліметтерді өңдеу
Ол кестедегі мәліметтердің негізінде түрлі-түсті диаграмммалар тұрғызып, жүргізуді қамтамасыз ете алады. Excel мүмкіндігінің көпжақтылығы...
The latest way: Run sas and the next moment you see a nice graph in an excel sheet (though it still requires some excel work to get the first excel graph done) iconExcel электрондық кесте құралдарымен мәліметтерді өңдеу
Ол кестедегі мәліметтердің негізінде түрлі-түсті диаграмммалар тұрғызып, жүргізуді қамтамасыз ете алады. Excel мүмкіндігінің көпжақтылығы...
The latest way: Run sas and the next moment you see a nice graph in an excel sheet (though it still requires some excel work to get the first excel graph done) iconMs excel-дің графиктік мүмкіндіктері
А бiлiмдiлік: ms excel-дің графиктік мүмкіндіктері туралы оқушыларға түсінік беріп, Диаграмма шеберінің көмегімен диаграммалар тұрғызуды...
The latest way: Run sas and the next moment you see a nice graph in an excel sheet (though it still requires some excel work to get the first excel graph done) iconИнформатика электронные таблицы ms excel 2003 (практикум)
Программа ms excel относится к одной из старейших групп программ электронные таблицы. Электронные таблицы предназначены для выполнения...
Разместите кнопку на своём сайте:
Документы


База данных защищена авторским правом ©kzdocs.docdat.com 2012
обратиться к администрации
Документы
Главная страница