Copying From SAS Column Titles to Excel

[alc]

I use SAS to open large files from which I copy small amounts of data and paste it into Excel. My question is: is it possible to copy the column titles from SAS and paste them into an Excel file.

Thank you for answering this question and also for keeping the answer simple as I am not a skilled SAS user.

Thanks

Alc

[art297]

I think the following will create what you are looking for.  Both the libname and memname (i.e., file name) must be in upper case:

proc sql;

  create table temp as

    select name

      into :reorder

        separated by ' '

          from dictionary.columns

            where libname="SASHELP" and

              memname="CLASS"

  ;

quit;

proc transpose data=temp out=tocopy (keep=col:);

  var name;

run;

[alc]

Will give it a try tomorrow.

Thank you

Al Charbonneau

[Linlin]

Hi Art,

I made change to your code. " into :reorder   separated by ' ' is not necessary.  Thank you!

proc sql;

  create table temp as

    select name

        from dictionary.columns

            where libname="SASHELP" and

              memname="CLASS";

proc transpose data=temp out=tocopy (keep=col:); 

  var name;

run;

Linlin

[art297 · Accepted solution]

Linlin, First of all, Happy New Year!  Second, as usual, you are absolutely correct.  As you may have noticed, those statement were residuals from an earlier post that also used dictionary.columns as the base of a solution.  Fortunately, while totally redundant, they don't hurt the desired solution.

[alc]

Good Morning

Thank you for weighing in on my question.  I have an additional question but first let me try to explain what I am doing.  I go to the local university's computer lab and open a file in SAS.  Some of these files have 500-600 columns.  Of the thousands of rows, I normally copy 250-300 rows and paste the data into an Excel file where I do simply math (averages, rates of increase, etc.).  So my question is what happens after I type your code into the SAS program?  For example, does it open an Excel file with the column titles or does it "load" up Excel's copy/past function?

I will try yiour suggestion later on today at the lab.

Thanks for your advice.

Al Charbonneau

[art297]

Different question then you originally asked.  To accomplish the entire task, including both the column names and the data, try the following code:

PROC EXPORT DATA= ReplaceThisWithYourFilenameE.G.sashelp.class (obs=350)

            OUTFILE= "C:\want.xlsx"

            DBMS=EXCEL REPLACE;

     SHEET="first350";

RUN;

The above code would created an xlsx file containing the column names and the first 350 records.  The type listed after dbms= is dependent upon the version of SAS you are using and the type of computer it is running on. You can get SAS to pick out the correct answers, and build the code for you, by selecting export from the file menu.

[alc]

You are right - it is a different question.  Would it be simpler (for me) to just copy the column titles, which is what I really want, to excel? 

When it comes to SAS, the simpler the better for me.

Thanks again

Al

[art297]

If you only want to "copy" the column headings, I would use proc export to export the file created by the proc sql code/proc transpose solution I had originally proposed.

[alc]

Good Morning

Frankly, I have been trying to (1) eliminate the need to work in SAS because of my skill level and (2) find a work around where I can export data to Excel where I have a few more skills.  Neither has worked so I would like to review our discussion which began on Jan 22 of this year.  I now understand a bit more about the SAS files that I am working with.  The files contain over 6000 rows of data with upwards of 300 columns.  I would like to copy the column titles and 300 or so rows, which are in sequence, and send them to an Excel file. The rows are in sequence but they will begin, for example, at 1600 and run to 1900.  The beginning and ending row varies by year.  If this turns out to be too much for Excel to handly, I could boil the selection to 30 or so rows with column titles.

At the time of my original inquiry, you recommended:

PROC EXPORT DATA=ReplaceThisWithYourFilenameE.G.sashelp.class (obs=350)

          OUTFILE= "C:\WANT.XLSX"

          DBMS=EXCEL REPLACE;

          SHEET="First350";

RUN

At the time it was not clear to me how the data were organized in the SAS file so I didn't mention the idea that the data I want are in sequence but not at the beginning of the file.  So I suspect I need to have this command modified so I can insert a beginning row and ending row. 

Thank you in advance for your thoughts/comments.  Please do not assume that I have any skill level in SAS.

Al

[art297]

Depending upon the versions of Excel and SAS that you are using, you may have to change the dbms= statement to excelcs.

However, to select rows 1600 thru 1900, you could use the firstobs= and obs= options.  e.g.:

PROC EXPORT DATA=ReplaceThisWithYourFilenameE.G.sashelp.class

            (firstobs=1600 obs=1900)

          OUTFILE= "C:\WANT.XLS"

          DBMS=EXCEL REPLACE;

          SHEET="Rows1600_thru_1900";

RUN

[alc]

Hi

I was unable to get the program to run. Would appreciate knowing what I did wrong.   Here is the error messag:

NOTE: Copyright (c) 2002-2008 by SAS Institute Inc., Cary, NC, USA.

NOTE: SAS (r) Proprietary Software 9.2 (TS2M2)

      Licensed to UNIVERSITY OF RHODE ISLAND (T&R), Site 70008099.

NOTE: This session is executing on the W32_VSPRO  platform.

NOTE: SAS initialization used:

      real time           3.86 seconds

      cpu time            0.71 seconds

NOTE: PROCEDURE EXPORT used (Total process time):

      real time           0.06 seconds

      cpu time            0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.

1    PROC EXPORT DATA=pps90.sas7bd(obs=350)

2                OUTFILE= "C:\want.xlsx"

3                DBMS=EXCEL REPLACE;

4         SHEET="first350";

5    RUN;

NOTE: Starting the Analyst Application.

*****

***** Important Note: The Analyst application will no longer be available after SAS Release 9.2.

*****

NOTE: Analyst has ended.

I tried the other program and was unable to get it to run also.  Unfortunately, I don't seem to be able to paste it in this message.  Will send it in another message.

Thank you.

Al

[art297]

Your log file doesn't provide enough info.

The first thing I would try is to leave off the extension from the data-= part.  i.e., rather than state:

DATA=pps90.sas7bd(obs=350)

try it with: DATA=work.pps90 (obs=350)

My next suggestion would depend upon whether your system is 32 or 64 bit.

i.e., after trying the above, the next thing I would change is using DBMS=EXCELCS rather than EXCEL.

[art297]

And, one additional thought.  Combined with my other suggestions, you could also try to export just an xls file, rather than an xlsx file (i.e., change the extension on the output file).

[alc]

Hi

Here is the second error message:

NOTE: Copyright (c) 2002-2008 by SAS Institute Inc., Cary, NC, USA.

NOTE: SAS (r) Proprietary Software 9.2 (TS2M2)

      Licensed to UNIVERSITY OF RHODE ISLAND (T&R), Site 70008099.

NOTE: This session is executing on the W32_VSPRO  platform.

NOTE: SAS initialization used:

      real time           3.86 seconds

      cpu time            0.71 seconds

NOTE: PROCEDURE EXPORT used (Total process time):

      real time           0.06 seconds

      cpu time            0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.

1    PROC EXPORT DATA=pps90.sas7bd(obs=350)

2                OUTFILE= "C:\want.xlsx"

3                DBMS=EXCEL REPLACE;

4         SHEET="first350";

5    RUN;

NOTE: Starting the Analyst Application.

*****

***** Important Note: The Analyst application will no longer be available after SAS Release 9.2.

*****

NOTE: Analyst has ended.

NOTE: Data file _EXP0_.PPS90.DATA is in a format that is native to another host, or the file

      encoding does not match the session encoding. Cross Environment Data Access will be used,

      which might require additional CPU resources and might reduce performance.

6    PROC EXPORT DATA=pps90.sas7bd

7

NOTE: PROCEDURE EXPORT used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.

8                (firstobs=1600 obs=1900)

9

10             OUTFILE= "C:\WANT.XLS"

11

12             DBMS=EXCEL REPLACE;

13

14             SHEET="Rows1600_thru_1900";

15

16   RUN

Thank you

Al