Libname statement to export to excel.....

Reply
Occasional Contributor
Posts: 12

Libname statement to export to excel.....

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

Respected Advisor
Posts: 3,124

Re: Libname statement to export to excel.....

Show your code and log. Elsewise are all guessing.

Occasional Contributor
Posts: 12

Re: Libname statement to export to excel.....

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!

Respected Advisor
Posts: 3,124

Re: Libname statement to export to excel.....

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

Occasional Contributor
Posts: 12

Re: Libname statement to export to excel.....

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

Respected Advisor
Posts: 3,124

Re: Libname statement to export to excel.....

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.

Occasional Contributor
Posts: 12

Re: Libname statement to export to excel.....

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!

Respected Advisor
Posts: 3,124

Re: Libname statement to export to excel.....

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


Occasional Contributor
Posts: 12

Re: Libname statement to export to excel.....

Man - you are good!

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

Thank you for your help

Ask a Question
Discussion stats
  • 8 replies
  • 515 views
  • 0 likes
  • 2 in conversation