topic Copying From SAS Column Titles to Excel in SAS Procedures

Copying From SAS Column Titles to Excel

alc — Sat, 21 Jan 2012 19:58:43 GMT

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

Copying From SAS Column Titles to Excel

art297 — Sat, 21 Jan 2012 20:39:36 GMT

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;

Copying From SAS Column Titles to Excel

alc — Sat, 21 Jan 2012 22:42:43 GMT

Will give it a try tomorrow.

Thank you

Al Charbonneau

Copying From SAS Column Titles to Excel

Linlin — Sun, 22 Jan 2012 04:07:20 GMT

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

Re: Copying From SAS Column Titles to Excel

art297 — Sun, 22 Jan 2012 04:10:29 GMT

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.

Copying From SAS Column Titles to Excel

alc — Sun, 22 Jan 2012 13:39:30 GMT

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

Copying From SAS Column Titles to Excel

art297 — Sun, 22 Jan 2012 14:16:11 GMT

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.

Copying From SAS Column Titles to Excel

alc — Sun, 22 Jan 2012 14:38:40 GMT

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

Copying From SAS Column Titles to Excel

art297 — Sun, 22 Jan 2012 14:42:12 GMT

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.

Copying From SAS Column Titles to Excel

alc — Fri, 24 Feb 2012 12:39:44 GMT

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.

Copying From SAS Column Titles to Excel

art297 — Fri, 24 Feb 2012 13:26:24 GMT

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

Copying From SAS Column Titles to Excel

alc — Fri, 24 Feb 2012 13:35:18 GMT

Thank you for responding to my inquiry.

The local university has allowed me to use SAS in their computer lab. As I recall, it is SAS 9.2. I think I also have been in SAS Enterprise?

I should be there later on today and I could confirm the 9.2 designation.

Thanks again for responding.

Copying From SAS Column Titles to Excel

alc — Sat, 25 Feb 2012 00:38:37 GMT

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

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.

Copying From SAS Column Titles to Excel

alc — Sat, 25 Feb 2012 00:42:36 GMT

Here is the second error message:

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

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)

10 OUTFILE= "C:\WANT.XLS"

12 DBMS=EXCEL REPLACE;

14 SHEET="Rows1600_thru_1900";

16 RUN

Thank you

Re: Copying From SAS Column Titles to Excel

art297 — Sat, 25 Feb 2012 00:55:39 GMT

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.

Re: Copying From SAS Column Titles to Excel

art297 — Sat, 25 Feb 2012 01:23:51 GMT

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).

Copying From SAS Column Titles to Excel

alc — Sat, 25 Feb 2012 19:01:57 GMT

I am at the University. This program has been running for more than 10 minutes. It says autosave complete on the bottom left hand corner of the screen. When I click on Output - (Untitled) I get a pulsing cursor and a blank screen. How will I know when the program is finished?

Thanks

Re: Copying From SAS Column Titles to Excel

art297 — Sat, 25 Feb 2012 19:12:21 GMT

Are you running it from base SAS or EG? Also, rather than looking at the output screen, take a look at the log.

Re: Copying From SAS Column Titles to Excel

Tom — Sat, 25 Feb 2012 19:47:43 GMT

If you copied the whole log it is waiting for you to submit a semi-colon to end the RUN statement so it can start the PROC.

Re: Copying From SAS Column Titles to Excel

alc — Sat, 25 Feb 2012 19:55:12 GMT

Thanks for replying.

Now it gives me the following error message:

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 0.96 seconds

cpu time 0.76 seconds

1 PROC EXPORT DATA=work.pps90

3 (firstobs=1600 obs=1900)

5 OUTFILE= "C:\WANT.XLS"

7 DBMS=EXCEL REPLACE;

9 SHEET="Rows1600_thru_1900";

11 RUN

12 PROC EXPORT DATA=work.pps90

14 (firstobs=1600 obs=1900)

16 OUTFILE= "C:\WANT.XLS"

18 DBMS=EXCEL REPLACE;

ERROR: Unrecognized form of the RUN statement. Use either RUN; or RUN CANCEL;.

NOTE: The previous statement has been deleted.

20 SHEET="Rows1600_thru_1900";

22 RUN;

ERROR: Connect: The Microsoft Access database engine cannot open or write to the file ''. It is

already opened exclusively by another user, or you need permission to view and write its

data.

ERROR: Error in the LIBNAME statement.

Connection Failed. See log for details.

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

NOTE: PROCEDURE EXPORT used (Total process time):

real time 1:05:54.15

cpu time 2.54 seconds

Do you have a suggestion?

Thanks