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

[Zachary]

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.

[ballardw]

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.

[Zachary]

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.

[ballardw · Accepted solution]

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.

[Zachary]

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.

[ChrisHemedinger]

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

[Zachary]

Thank you for all of the suggestions!