Hi all,   Is there any user define range option in ODS TAGSETS.EXCELXP. Below set of code is submitted.   ODS TAGSETS.EXCELXP FILE="D:\Report.xls" STYLE=mystyle options(sheet_interval='none' sheet_name='CLASS' frozen_headers='yes'); OPTIONS MISSING=''; proc report data=class nowindows style(header)=[fontweight=bold BACKGROUND=BIGB FOREGROUND=black fontsize=8pt font=(calibri, 8pt) ] style(report)=[fontweight=bold BACKGROUND=BIGB FOREGROUND=black fontsize=8pt font=(calibri, 8pt) ] ; columns name sex age height weight sum_height_weight; define name/'student/ name/stansys' center style={just=c fontsize=8pt font=(calibri, 8pt) vjust=m}; define sex/order style={just=c fontsize=8pt font=(calibri, 8pt) vjust=m}; define age/ display 'age' style={just=c fontsize=8pt font=(calibri, 8pt) vjust=m}; define sum_height_weight/'heigt-weight' computed style={just=c fontsize=8pt font=(calibri, 8pt) vjust=m}; define height /style={just=c fontsize=8pt font=(calibri, 8pt) vjust=m}; define weight/ style={just=c fontsize=8pt font=(calibri, 8pt) vjust=m}; /*sub total*/ break after sex/summarize; compute after sex; sex=catx('-',sex,'total'); endcomp; /*grand total*/ rbreak after/summarize; compute after; sex='Grand Total'; endcomp; compute sum_height_weight; sum_height_weight=sum(height.sum,weight.sum); endcomp; run; ods tagsets.excelxp close;     The default output is started in the excle sheets from (A1-Range), But i want to change starting from (B1-Range). Any help would be greatly appreciated. ... View more

Hi all, I am using sas enterprise guide 5.1 version with MS Office-2010. I want to export two datasets into excel with 2 different sheets, I tried proc export proc export data=dataset1 outfile="filename.xlsx" dbms=xlsx replace; sheet="salaries"; run; proc export data=dataset2 outfile="filename.xlsx" dbms=xlsx replace; sheet="department"; run;   After this i am keeping this file in zip file and sending automailer. ods package(Accounts) open nopf; ods package(Accounts) add file="filaname.xls"; ods package(Accounts) publish archive properties(archive_name="Accounts.zip" archive_path="/SAS/BIU/INPUT"); ods package(Accounts)  close;   But in mail only the second sheets appears, is there any solution of exporting two sheets in excel. In this dbms=excel and excelcs are not working. Any help would be greatly appreciated ... View more

Hi all, I have data in excel, that contains special characters, when importing into sas instead of special characters   ≥  ≤ it reading only equals to symbol (=). how to import this data with actual data. I follow below code for this.   /*1st method*/ proc import datafile="D:\sas\book1.xlsx" out=data dbms=excel replace; getnames=yes; scantext=yes; mixed=yes; run; /*2nd method*/ libname data excel "D:\sas\book1.xlsx"; data sheet_data; set data."sheet1$"n; run; ... View more

Hi all,   I have the data of Age, Sex, Height, assume age is character variable. In proc report taken group variabe as age, this age i need to display their gender and sum of height. Actual report and final output is attached in image format.   what i need is if group variable contains single row then instead of showing that row, group variable is sufficient.   I followed below code for this, but there is lot of steps in my code. Is there any method to reduce the code in proc report it self.     proc sql; create table class_mod as select distinct *, count(age) as count from sashelp.class group by age; quit; data class; retain age1 sex height; set class_mod; drop age; age1=input(age,$20.); rename age1=age; keep age1 sex height count; where age in (14,15,16); run; proc report data=class nowd list out=report_class; column age sex height count; define age/ group; DEFINE Height / SUM; break after age/summarize ; compute after age; age=catx('-','Avg-value for',age); endcomp; rbreak after/summarize; compute after; age='Grand total'; endcomp; run; data report_class; set report_class; if count=1 and _BREAK_=" " then delete; run; proc report data=report_class nowd list out=report_class1; column age sex height; define age/ group order=data; run;   ... View more

Can any one explain the inner join on condition (ON a.date=b.DATE - 1 YEAR) in below query proc sql; alter table k.data_products add var1 NUM(8) add var2 NUM(8) add var3 NUM(8) add var4 NUM(8) add var5 NUM(8); update k.data_products1 a set a.var1=b.c_var1,a.var2=b.c_var2,a.var3=b.c_var3 FROM k.data_products1 a INNER JOIN k.data_products2 b ON a.date=b.DATE - 1 YEAR; ... View more

Hi all, how to bulid a predictive model for categorical variables in  sas 9.3 and which predictive model is best  ... View more

Hi good morning all,   I have data of 3 departments sales, marketing, accounts data. When exporting these data into excel, my desired output looks like of in the first sheet I need 3 rows of data i.e.: sales, marketing, accounts. These 3 rows contains hyperlinks to the next 3sheets When I click into sales it will takes me into the sales sheet as well as remaining. Can any one help how this can be done? Any help would be greatly appreciated ... View more