Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 115
Accepted Solution

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

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:

Capture1.JPG

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

Capture2.JPG

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.


Accepted Solutions
Solution
‎05-05-2015 01:25 PM
Grand Advisor
Posts: 10,210

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

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.

View solution in original post


All Replies
Grand Advisor
Posts: 10,210

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

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.

Frequent Contributor
Posts: 115

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

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.

Solution
‎05-05-2015 01:25 PM
Grand Advisor
Posts: 10,210

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

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.

Frequent Contributor
Posts: 115

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

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.

Community Manager
Posts: 2,695

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

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;

freq.png

Chris

Frequent Contributor
Posts: 115

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

Thank you for all of the suggestions!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 879 views
  • 6 likes
  • 3 in conversation