turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- Libname statement to export to excel.....

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-19-2013 03:24 PM

I'm trying to export a dataset output to excel using the libname statement but I keep getting a message that reads "corrupted file" and when I'm finally able to open it the output is not there.

I know how to use proc export and it actually works perfectly but I'd like to know how to do it with Libname.

Thank you

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-19-2013 03:59 PM

Show your code and log. Elsewise are all guessing.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-19-2013 04:06 PM

libname mylib 'E:\analysis.xls';

**data** Q2;

set mylib."pulserate$"n;

drop patient_id pulse_rate;

**run**;

**proc** **sort** data=Q2; by blood_pressure ;**run**;

**data** Q2_1(keep = median_bp);

last_blood_pressure=blood_pressure;

retain median_bp;

set Q2 end=lastobs nobs=n;

by blood_pressure;

if mod(n,**2**) = **0** and _n_ = (n+**2**)/**2** then do ;median_bp=(blood_pressure + last_blood_pressure)/**2**; end;

if lastobs then output;

**run**;

The excel file 'analysis' contains a sheet called 'pulserate' which contains 3 variables and I'd like to find the median for the variable blood_pressure which I think i did with the code. But I want to output the new median_BP variable to excel using libname but I find that the file I'm able to create iin excel has no data and always show a message of 'read-only'.

Thanks!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-19-2013 04:46 PM

I still can't see your second libname statement for output to Excel files. Anyway, here is what I generally do if I use libname excel engine:

libname test PCFILES path="c:\temp\test.xls"; |

data test.class;

set sashelp.class; | |

run; |

libname test clear;

The reason I use PCFILES instead of EXCEL is because I am running 64bit SAS when my excel is 32bit. Otherwise, you can just do:

libname test "c:\temp\test.xls";

HTH, but if I hit the wrong target, you need to show the part of your code to export from SAS to excel, and the error log if there is any.

Haikuo

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-19-2013 05:12 PM

First of all thanks for your help.

What I'm trying to send to excel is the median_bp which I calculated through this:

**data** Q2_1(keep = median_bp);

last_blood_pressure=blood_pressure;

retain median_bp;

set Q2 end=lastobs nobs=n;

by blood_pressure;

if mod(n,**2**) = **0** and _n_ = (n+**2**)/**2** then do ;median_bp=(blood_pressure + last_blood_pressure)/**2**; end;

if lastobs then output;

**run**;

I just dont know how to create the file with the libname statement.

my log looks like this:

libname mylib clear;

WARNING: Libname MYLIB is not assigned.

31 libname mylib path="E:\temp\test.xls";

ERROR: Libname MYLIB is not assigned.

ERROR: Error in the LIBNAME statement.

32 data Q2_1(keep = median_bp);

33 last_blood_pressure=blood_pressure;

34 retain median_bp;

35 set Q2 end=lastobs nobs=n;

36 by blood_pressure;

37 if mod(n,2) = 0 and _n_ = (n+2)/2 then do ;median_bp=(blood_pressure + last_blood_pressure)/2;

37 ! end;

38 if lastobs then output;

39 run;

NOTE: There were 16 observations read from the data set WORK.Q2.

NOTE: The data set WORK.Q2_1 has 1 observations and 1 variables.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

cpu time 0.00 seconds

40 libname mylib clear;

WARNING: Libname MYLIB is not assigned

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-19-2013 05:38 PM

It seems to me that your syntax is not correct, if you are running 32bit SAS, just do:

libname test "c:\temp\test.xls";

No "path=", that is for another engine, when your SAS is 64bit while your office product is 32bit or vise versa, do this:

libname test PCFILES path="c:\temp\test.xls";

So I guess I will take responsibility of causing this confusion.

On another note, since you have posted your code twice, you did have my attention. The efficiency of your code can be vastly improved by doing the following, ( I don't know why you call your new variable MEDIAN, in fact, it is just a mean of two blood pressure values around central zone, and you only collect this value from dataset of even obs)

data q2_1 (keep=median_bp);

if mod(n,2) ne 0 then stop;

do i=1 by -1 to 0;

p=(n+2)/2-i;

set q2 nobs=n point=p;

if i=1 then _bp=blood_pressure;

end;

median_bp=mean(blood_pressure,_bp);

output;

stop;

run;

Now you only read 0-2 records instead of the whole data set, and if you are doing billion records data (BIG data, they say) like I am, this simple upgrade will save half day of the running time.

HTH,

Haikuo

update: It occurred to me that your code did not output to excel even it runs without error. It output to WORK library. So instead of "data q2_1 (keep=median_bp);", you should go with: "data Mylib.q2_1 (keep=median_bp);" Mylib is the libref pointing to the Excel file "test.xls", without referring it, your libname statement becomes irrelevant.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-19-2013 05:57 PM

libname mylib 'E:\Analysis.xls';

data Q2;

set mylib."pulserate$"n;

drop patient_id pulse_rate;

run;

proc sort data=Q2; by blood_pressure ;run;

data q2_1 (keep=median_bp);

if mod(n,2) ne 0 then stop;

do i=1 by -1 to 0;

p=(n+2)/2-i;

set q2 nobs=n point=p;

if i=1 then _bp=blood_pressure;

end;

median_bp=sum(blood_pressure,_bp)/2;

output;

stop;

run;

libname test "c:\temp\test.xls";

data test.class;

set sashelp.class;

run;

libname test clear

First let me thank you for your help and advise. 2nd this is what I have so far and I really dont understand your directions to create the file in excel using libname.

help - thanks so much!

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-19-2013 06:07 PM

This part of code:

libname test "c:\temp\test.xls";

data test.class;

set sashelp.class;

run;

libname test clear

Is just to show you how the libname works in general, you need to change some names to make it work in your context: please note the CAPTITAL:

libname test "YOUR_OUTPUT.xls";

data TEST.OUTPUT;

set Q2_1;

run;

libname test clear

It is Sunday afternoon, but it feels like Monday morning. I guess we all some coffee.

Haikuo

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

05-19-2013 06:20 PM

Man - you are good!

If you live in NY coffee is on me (seriously).

Thank you for your help