Help using Base SAS procedures

Convert sas datasets into Excel files

Reply
N/A
Posts: 0

Convert sas datasets into Excel files

Hi,

I am facing problem in converting sas datasets into excel files when zero observations in sas datasets.
I have sas dataest with 10 variables and no observations when i convert into excel file using ods html the excel is creating but no variable names just blank exel. but if i used a dataset with 1 observation excel is creating fine. I want to create the excel with variable names even no observations in dataset. Please let me know if any options are present or any methods is present.

Thanks in advance ,
Murali Message was edited by: murali.vijayendran@gmail.com
N/A
Posts: 0

Re: Convert sas datasets into Excel files

You can export your SAS data to excel format in File menu.
But you must run your data set first even with no data. Then export your SAS dataset into excel.
Hope this can help you. Maybe there is another way that others SAS user know.

Rgrds,

Yaumil
Contributor
Posts: 61

Re: Convert sas datasets into Excel files

Hello Murali.,

Use this u can get the blank dataset into excel sheet.

data a;
input a b c d e f g h i j;
cards;
proc print;
run;
proc export data=work.a
outfile="C:\abcd\a.xls"
dbms=excel2000
replace;
run;

Venky
N/A
Posts: 0

Re: Convert sas datasets into Excel files

Hi

I didn't like offering the absent worksheet as indication there are no exceptions. Better, seemed the idea of a worksheet with one "missing" line. That satisfied the "difficult" client-user. The code involved this brief, but unusual data step. It is executed just before the proc print[pre] option _last_ = dataset.toBePrinted ;
data &syslast ;
if nobs then stop ;
output ;
modify &syslast nobs=nobs ;
output ;
run ; [/pre]
As you see, the step involves almost no data-set specific details, but creates a missing observation. However, it assumes your data set will provide the NOBS information and can be MODIFY-ed.
Perhaps you should try it on yours.
I packaged mine as a SAS macro like:[pre] %macro IFz1(data=&syslast) /des= 'if Zero make 1 obs' ;
data &data ;
if nobs then stop ;
output ;
modify &data nobs=nobs ;
stop ;
run ;
%mend IFz1 ;[/pre]Which is used like[pre] %IFz1( data=mylib.mydataset) ;[/pre]and when it is the latest data set that I need to update, the syntax becomes even simpler[pre] %ifz1 [/pre]

PeterC
N/A
Posts: 0

Re: Convert sas datasets into Excel files

Hi,

Well the above solutions are sufficient. You can use PROC EXPORT for the required output.
But the fact here is if you want to export the metadata and you use PROC PRINT with ODS HTML or ODS CSV, you won't get the output if there is no data in the dataset.
This is because ODS just redirects the output of the SAS output window to another output destination. Here you can figure out easily that using PROC PRINT on a empty dataset gives you no output on the SAS output window, and hence an empty excel file.

Just take care of this fact while using ODS.

Thanks,
Saurabh.
Ask a Question
Discussion stats
  • 4 replies
  • 2096 views
  • 0 likes
  • 2 in conversation