topic Re: Copying Output and Pasting in Excel - Top Row Not Perfectly Lining Up in SAS Enterprise Guide

Copying Output and Pasting in Excel - Top Row Not Perfectly Lining Up

Zachary — Tue, 05 May 2015 14:36:18 GMT

I am doing a very basic PROC FREQ such as the following:

proc freq data = SQUADS;

tables SVAR1 * SMNQ_D_POST_CODE

nopercent nocum norow nocol;

run;

The results look very normal:

And it usually looks pretty normal when I paste it into Excel:

If I go to cell A5 (the 1887) then arrow on over to the right the first row of 0 0 5 0 5 lies on row 4 instead of row 5. Yes, this is a trifle in the grand scheme of things, but it is a little annoying.

Is this the result of how I have set up my output to look? I am open to any suggestions. For now I am just cognizant of this when I work between platforms.

Thank you.

Re: Copying Output and Pasting in Excel - Top Row Not Perfectly Lining Up

ballardw — Tue, 05 May 2015 14:48:39 GMT

More the behavior of copy from HTML and paste into Excel in general. What has happened is that in the paste the text vertical alignment is top. The cells are merged vertically so they appear in the wrong row. Options are to manually align the text vertically after pasting or use an approach that doesn't paste but generates a more appropriate Excel formatted document such as ODS tagsets.excelxp.

Re: Copying Output and Pasting in Excel - Top Row Not Perfectly Lining Up

Zachary — Tue, 05 May 2015 15:45:20 GMT

Thank you very much. I did a little investigating using the ODS tagsets.excelxp recommendation you provided. I am thinking it probably would be easiest to simply create the output in such a way so that it is .csv.

Is there an option to select in EG that would enable this? Or do I need to go through the processes of downloading the proper program/functionality and write the extra code?

Trying to keep as simple as possible.

Thank you.

Re: Copying Output and Pasting in Excel - Top Row Not Perfectly Lining Up

ballardw — Tue, 05 May 2015 17:25:39 GMT

Options and methods vary depending on the version of SAS / Enterprise Guide.

Generally instead of Proc Freq I would look to either Proc Report or Proc Tabulate for similar output as those procedures allow overrides and additional options such as text alignment, weight, font size and host of other appearance options. Or direct the Proc Freq output to a data set and use Proc Print with also allows more appearance controls.

Put I would start with a simple ODS output and see if the results are satisfactory.

Re: Copying Output and Pasting in Excel - Top Row Not Perfectly Lining Up

Zachary — Tue, 05 May 2015 18:53:45 GMT

Thank you so much!!! I believe it is the former SPSS-er in me that realy wanted proc freq to work. I will try all of those suggestions.

Re: Copying Output and Pasting in Excel - Top Row Not Perfectly Lining Up

ChrisHemedinger — Tue, 05 May 2015 20:34:05 GMT

One more thing you might look at: the LIST option on the TABLES statement. The output will be cleaner, but oriented differently.

proc freq data=sashelp.cars;
tables origin * type /  nopercent nocum norow nocol LIST; 
run; 

Chris

Re: Copying Output and Pasting in Excel - Top Row Not Perfectly Lining Up

Zachary — Wed, 06 May 2015 16:12:28 GMT

Thank you for all of the suggestions!