proc export

SASPhile — Thu, 23 Apr 2009 18:06:32 GMT

Physician product year scripts
Mark Novo 2007 3000
Mark Novo 2007 4000
Mark Feib 2007 2500
Mark Novo 2008 2500
Twain Novo 2007 2000
Twain Novo 2008 3000
Twain Novo 2008 6000

How to generate the above data to following way ?

2007 2008
Physician Novo Feib Novo Feib
Mark 7000 2500 2500 -
Twain 2000 - 9000 -

Re: proc export

sbb — Sat, 25 Apr 2009 18:40:06 GMT

Investigate SAS PROC TABULATE, or the SAS DATA step can generate such a formatted-output.

Report Creation Using Data _NULL_
Caroline Bahler, Meridian Software, Inc., Raleigh NC
Eric Brinsfield, Meridian Software, Inc., Raleigh NC
http://www2.sas.com/proceedings/sugi27/p061-27.pdf

Several search-matches using the Google advanced search argument below:

proc tabulate reporting site:sas.com

SAS support http://support.sas.com/ website provides SAS-hosted documentation and technical / conference reference material suitable to your post topic.

Scott Barry
SBBWorks, Inc.

Re: proc export

Cynthia_sas — Sat, 25 Apr 2009 21:23:56 GMT

Hi:
There's a difference between wanting to export a data set (which you would get with PROC EXPORT) and wanting a report (which you would get with PROC REPORT). The results of a report procedure could be routed to HTML, CSV, or other destinations. However, you can only use a dataset with PROC EXPORT.

When you used this same data with a question about PROC REPORT:
http://support.sas.com/forums/thread.jspa?threadID=5625&tstart=0 you seemed to want a report. Now you ask about an output dataset. Proc Export will not restructure or summarize your data from its original structure to the new structure that you show.

If your data are in one structure and you need the -data- to be in another structure, then the tools at your disposal are PROC TRANSPOSE, DATA _NULL_, PROC SQL, etc.

However, your example shows a summarization, as well as a simple restructuring of data. For example... your original data shows
[pre]

Physician product year scripts
Mark Novo 2007 3000
Mark Novo 2007 4000
Mark Feib 2007 2500
Mark Novo 2008 2500
Twain Novo 2007 2000
Twain Novo 2008 3000
Twain Novo 2008 6000
[/pre]

while your desired output (report? dataset?) shows:
[pre]
2007 2008
Physician Novo Feib Novo Feib
Mark 7000 2500 2500 -
Twain 2000 - 9000 -

[/pre]

To me, the above still looks like an output REPORT, because you show each year -above- each drug name, in a more report-like fashion, and you show a dash
(-) instead of a .or a 0 for years when there were no scripts.

I would expect that an output DATASET would be structured and have variable names like this:
[pre]
Physician Novo2007 Feib2007 Novo2008 Feib2008
Mark 7000 2500 2500 .
Twain 2000 . 9000 .

[/pre]

If you want a REPORT showing the physician names going down the rows and then showing years and products going across the columns, your choices for a report are to use either PROC REPORT or PROC TABULATE. You could use PROC MEANS and then transpose, but either REPORT or TABULATE will give you a summary report in the structure you want.

If you want an output dataset, you still have the above tools at your disposal. You also have the ability to create output datasets from PROC REPORT and PROC TABULATE. However, you may find that the output datasets are not quite in the structure or have the variables named as you might want.

The code below shows you some REPORT or TABULATE code to generate a report. Each step creates an output dataset. At the bottom of the two steps are PROC PRINTS of the output dataset created by each procedure. Either of these datasets (WORK.TABOUT or WORK.REPOUT could be exported with PROC EXPORT.) Or, since you can route your PROC REPORT or PROC TABULATE output to an HTML file, an RTF file, a PDF file, a CSV file or an Excel Spreadsheet Markup Language file, you may discover that you don't need PROC EXPORT, at all.

cynthia
[pre]
data phys;
length Physician $16 Product $20;
infile datalines;
input Physician $ product $ year scripts ;
return;
datalines;
Mark Novo 2007 3000
Mark Novo 2007 4000
Mark Feib 2007 2500
Mark Novo 2008 2500
Twain Novo 2007 2000
Twain Novo 2008 3000
Twain Novo 2008 6000
;
run;

options nodate missing=0;

** Without using PROC EXPORT, use ODS to create files that can be;
** opened and rendered with different programs:;
** File Created with Open with ;
** HTML ODS MSOFFICE2K Internet Explorer, Word or Excel ;
** CSV ODS CSVALL Excel or Lotus or any pgm that reads CSV ;
** RTF ODS RTF Word or any word processor ;
** PDF ODS PDF Acrobat Reader or any PDF-viewer ;
** XML ODS TAGSETS.EXCELXP Excel 2002 or higher Note that type of ;
** XML created conforms to Excel 2002/2003 ;
** Spreadsheet Markup Language specification ;
**;

ods msoffice2k file='c:\temp\physreport_ht.html' style=sasweb;

ods rtf file='c:\temp\physreport_wp.rtf';

ods pdf file='c:\temp\physreport_ar.pdf';

ods csvall file='c:\temp\physreport_plain.csv' style=sasweb;

ods tagsets.excelxp file='c:\temp\physreport_xp.xls'
options(embedded_titles='yes' absolute_column_width='10')
style=sasweb;

ods tagsets.excelxp options(sheet_name='Tabulate');
proc tabulate data=phys f=comma8.
out=work.tabout;
title 'Report with PROC TABULATE';
class physician product year;
var scripts;
table physician=' ',
year='Scripts Per Year'*product=' '*scripts=' '
/ box={label=Physician style={vjust=b}} row=float printmiss;
keylabel sum=' ';
run;

ods tagsets.excelxp options(sheet_name='Report');
proc report data=phys nowd
out=work.repout(rename=(_c2_=Feib2007 _c3_=Novo2007 _c4_=Feib2008 _c5_=Novo2008));
title 'Report with PROC REPORT';
column physician scripts,year,product;
define physician / group style(column)=Header;
define year / across ' ';
define product / across ' ';
define scripts / sum 'Scripts Per Year' f=comma8.;
run;
ods _all_ close;

** if none of the above output files are satisfactory, then you can;
** export either of these 2 files, created from the above procedures.;
ods html file='c:\temp\data_from_report_proc.html'
style=sasweb;

proc print data=work.tabout;
title 'Data set created by PROC TAB';
title2 'Would still need to transpose this before Export';
run;

proc print data=work.repout;
title 'Data set created by PROC REPORT';
title2 'This could be Exported as it is';
run;

ods _all_ close;

[/pre]

Re: proc export

SASPhile — Mon, 27 Apr 2009 14:01:25 GMT

Cynthia,
Thanks for your help.I will try one of your suggested methods.
Thanks again,
SASPhile

topic Re: proc export in SAS Programming

proc export

Re: proc export

Re: proc export

Re: proc export