topic Getting data totals and descriptive info in Excel output w/ proc export in Microsoft Integration with SAS

Getting data totals and descriptive info in Excel output w/ proc export

Kenbutsu — Thu, 17 Jan 2008 19:05:17 GMT

Hi,

Right now I create a data file in SAS, and I'm trying to show some summaries of the data (from different perspectives) so people can quickly determine if the data set is flawed in some way. Here are two examples of the summaries I create:

proc sql;
create table endsql1 as
select issyear,issmonth,aveissmonth,sum(count) as PolicyCount,sum(units) as Units,sum(amtinfrc) as AmountInforce,sum(statres) as StatReserve
from endmerge3
group by issyear,issmonth,aveissmonth;
quit;
proc export data = endsql1
dbms = excel2000
outfile = "J:\Val\Japan\Ord\20&crntqtr.\Prodfiles\SAScode\Summaries\Ord\&inforcefilename.&valqtr.END.xls"
replace;
sheet = 'IssueDate';
run;

proc sql;
create table endsql2 as
select modelage,issage,sum(count) as PolicyCount,sum(units) as Units,sum(amtinfrc) as AmountInforce,sum(statres) as StatReserve
from endmerge3
group by modelage,issage;
quit;
proc export data = endsql2
dbms = excel2000
outfile = "J:\Val\Japan\Ord\20&crntqtr.\Prodfiles\SAScode\Summaries\Ord\&inforcefilename.&valqtr.END.xls"
replace;
sheet = 'IssueAge';
run;

I would like to include, on another tab of this spreadsheet, the name & location of the program that produced it. Additionally, I would like these summaries to display totals of each data field on the last row. Does anyone know of a way to code this into SAS? I have searched for quite some time with no luck. Thanks in advance!

-Ken

Re: Getting data totals and descriptive info in Excel output w/ proc export

Cynthia_sas — Fri, 18 Jan 2008 00:11:57 GMT

Hi:
Here's one possible way to do it. Since you started with PROC EXPORT, I put the program in the workbook with PROC EXPORT.
1) Save this program as
c:\temp\savepgm.sas
(if you do not save it under this name, then step 3 won't work)
2) Step 1 and Step 2 are just modifications of your original proc SQL steps using SASHELP files.
3) Step 3 reads a .SAS program file into a SAS dataset and then uses PROC EXPORT to put the dataset into a worksheet.

[pre]
** Step 1;
proc sql;
create table endsql1 as
select region,
product,
sum(actual) as ActTot,
sum(predict) as PredTot
from sashelp.prdsale
group by region, product;
quit;

proc export data = endsql1
dbms = excel2000
outfile = "c:\temp\END.xls"
replace;
sheet = 'IssueDate';
run;

** Step 2;
proc sql;
create table endsql2 as
select country, product,
sum(actual) as ActTot,
sum(predict) as PredTot
from sashelp.prdsal2
group by country, product;
quit;

proc export data = endsql2
dbms = excel2000
outfile = "c:\temp\END.xls"
replace;
sheet = 'IssueAge';
run;

** Step 3: Read the program into a SAS data set;
filename wantpgm 'c:\temp\somepgm.sas';
data readpgm;
length linenum 8 pgmline $250;
infile wantpgm length=lg;
input pgmline $varying. lg;
linenum + 1;
run;

** write the program file -- with line numbers -- to a worksheet;
proc export data = readpgm
dbms = excel2000
outfile = "c:\temp\END.xls"
replace;
sheet = 'SomePgm';
run;

[/pre]

The first DATA step program in Step 3 reads whatever file you have specified in the FILENAME statement -- this could be ANY file, but it happens to be a .SAS program file.

There are only 2 variables in the SAS dataset -- linenum and pgmline. You don't really need linenum, but it's a good idea to have it in case you accidentally sort the program while it's in the worksheet -- you can get the original program back in order. Of course, if you have program lines longer than 250 characters, you'd have to change the LENGTH statement accordingly.

cynthia

Re: Getting data totals and descriptive info in Excel output w/ proc export

Kenbutsu — Fri, 18 Jan 2008 14:16:59 GMT

Cynthia,

Thank you for the reply. I'll try this and see how it goes.