topic Re: Print to excel from data step in SAS Programming

Print to excel from data step

GS2 — Fri, 21 Aug 2020 18:54:46 GMT

Hello,

I am running the following code within a macro:

title "&datain.";
footnote "&datain.";
data &dataout.;
set &dataout.;
file print;
array var {*} _numeric_;
do i=1 to dim(var);
name = vname(var(i));
value = var(i);
put @3 name @40 value /;
end;
drop i name;
run;

In the "file print" portion of this code is it possible to print to an excel file? My goal is to print this to excel and keep the formatting is created from this code. Thank you

Re: Print to excel from data step

Cynthia_sas — Fri, 21 Aug 2020 19:41:50 GMT

Hi @3 and @40 are pointer controls that are only respected by the LISTING destination when you use FILE PRINT. They would NOT work with ODS EXCEL or with the XLSX libname engine. So I'm not entirely sure what you mean when you say you want to "keep the formatting as created from this code".
Can you show what you expect your output to look like? Without data nobody can test your code and the challenge that I see is that you have &dataout as both the output data and the SET data, so that is troublesome to me because using the same file on both DATA statement and SET statement can lead to problems. Probably NOT a great issue since you are doing FILE PRINT, but right now, FILE PRINT as you show will only work for LISTING.
For example. this is what I get when I "unmacro" your code and use 2 obs from SASHELP.SHOES (I added region to the PUT statement):

You can use the DATA step to create the new structure (LONG) and then just use ODS EXCEL to create the output file:

Without worrying about pointer control and DATA step to FILE PRINT - -which would not work with ODS EXCEL or the LIBNAME engine anyway.

Cynthia

How do you envision this would look in Excel? If you want it in Excel, why not make a CSV file and then just open the CSV file in Excel???
Cynthia

Re: Print to excel from data step

GS2 — Fri, 21 Aug 2020 20:19:05 GMT

My Data looks like above when I run it through the macro. I am using &dataout because I am running 30+ data sets through this code; each by month and year. Ideally, in excel or csv my data would look like above, with each month and year combination on top, the data in the respective column and the variables along the side as shown.

An example of my data is:

data want;
input Complete_n partial_n com_par_percent no_res_percent Death_n Revised_n Conversion_n
declined_n tot_pat_for_comp clin_visit_percent xray_percent faam_percent ffi_percent
vr12_percent pt_percent md_percent vas_percent survey_percent;
cards;
59 200 50.6 49.4 10 23 10 0 512 36.9 40 32.5 64.5 64.5 63.5 43 70 53.5
;

Re: Print to excel from data step

Cynthia_sas — Fri, 21 Aug 2020 21:49:47 GMT

Hi:
Unless you use DDE (which is very very old Excel technology) with the DATA step, you will not be able to write directly to Excel as you envision. Writing a CSV file or using ODS EXCEL as I show in my second example is what you'll have to do.
Cynthia

Re: Print to excel from data step

ballardw — Mon, 24 Aug 2020 16:46:50 GMT

Reshape your data so you have a variable holding your current variable name and another variable with the value. Then you could use FILE PRINT ODS to print to two "columns" in Excel.

or use another procedure

proc tabulate data=want;
   var  Complete_n partial_n com_par_percent no_res_percent Death_n Revised_n Conversion_n
declined_n tot_pat_for_comp clin_visit_percent xray_percent faam_percent ffi_percent
vr12_percent pt_percent md_percent vas_percent survey_percent;

  table Complete_n partial_n com_par_percent no_res_percent Death_n Revised_n Conversion_n
declined_n tot_pat_for_comp clin_visit_percent xray_percent faam_percent ffi_percent
vr12_percent pt_percent md_percent vas_percent survey_percent ,
       max='Value'*f=best6.
       / box='Variable'
   ;
run;

If you have other variables like Year and Month you would modify that example to:

proc tabulate data=want;
   By Year Month;
   var  Complete_n partial_n com_par_percent no_res_percent Death_n Revised_n Conversion_n
declined_n tot_pat_for_comp clin_visit_percent xray_percent faam_percent ffi_percent
vr12_percent pt_percent md_percent vas_percent survey_percent;

  table Complete_n partial_n com_par_percent no_res_percent Death_n Revised_n Conversion_n
declined_n tot_pat_for_comp clin_visit_percent xray_percent faam_percent ffi_percent
vr12_percent pt_percent md_percent vas_percent survey_percent ,
       max='Value'*f=best6.
       / box='Variable'
   ;
run;

With the ODS EXCEL BODY= option to set the base file NEWFILE=BYGROUP you would get a separate file for each BY group , i.e. year and month.

The only macro bit needed would be the input data set and the base file name to use.

Re: Print to excel from data step

Cynthia_sas — Fri, 21 Aug 2020 22:35:30 GMT

He already has most of the program structure in place to reshape the data using his Array. I included an example of modifying his program in my posting and highlighted the few statements that would have to change in order to create a data set with 2 columns (and I added a few more columns just for ordering purposes -- but those could be left off).

Proc TABULATE is good and could go directly to ODS EXCEL, however using BY group processing would cause ODS EXCEL to make a new sheet for every unique year/month combination, which may or may not be what he wants.
Cynthia

Re: Print to excel from data step

ballardw — Fri, 21 Aug 2020 23:08:28 GMT

@Cynthia_sas wrote:
He already has most of the program structure in place to reshape the data using his Array. I included an example of modifying his program in my posting and highlighted the few statements that would have to change in order to create a data set with 2 columns (and I added a few more columns just for ordering purposes -- but those could be left off).

Proc TABULATE is good and could go directly to ODS EXCEL, however using BY group processing would cause ODS EXCEL to make a new sheet for every unique year/month combination, which may or may not be what he wants.
Cynthia

Yep, not very clear on the actual desired output.