求助：怎样使大量SAS数据完整导出到EXCEL里？

斑竹和各位高手可不可以教教我，怎样能把SAS中大的数据集全部导出到EXCEL里？因为变量过多，只能导出一半数据在EXCEL里。本人完全不懂SAS软件运用，是因为要打开SAS格式的数据才安装的SAS软件，这几天问了好多人都不会，很是郁闷，但写论文又要这个数据库里的数据，小妹跪求各位大侠了，本人再次在论坛上发求助贴，望能帮帮我！用最简单的方法。

wslirunping 发表于 2010-3-14 22:21
斑竹和各位高手可不可以教教我，怎样能把SAS中大的数据集全部导出到EXCEL里？因为变量过多，只能导出一半数据在EXCEL里。本人完全不懂SAS软件运用，是因为要打开SAS格式的数据才安装的SAS软件，这几天问了好多人都不会，很是郁闷，但写论文又要这个数据库里的数据，小妹跪求各位大侠了，本人再次在论坛上发求助贴，望能帮帮我！用最简单的方法。

The excel (2003 and before) limitations are
1) variables  = 256
2) obs (row) = 2**16

You have to put the data set to several sheets.

What is your limitation in this case.

对，数据的变量有480多个，我只能导出256个，我就是不知道怎么导出到几张表格里？望赐教！

wslirunping 发表于 2010-3-14 22:56
对，数据的变量有480多个，我只能导出256个，我就是不知道怎么导出到几张表格里？望赐教！

Here is an example.
Hope this helps.

data tmp;
array var(500);
do i = 1 to 500;
   var(i)=i;
end;
keep var:;
run;

proc print; run;

proc sql noprint;
select name  into: vlist1 separated by ' '
from dictionary.columns
where upcase(libname)='WORK' and upcase(memname)='TMP'
         and varnum<=256
         ;
select name  into: vlist2 separated by ' '
from dictionary.columns
where upcase(libname)='WORK' and upcase(memname)='TMP'
         and varnum>256
         ;
quit;

%put >>>> &vlist1 ;
%put >>>> &vlist2 ;


PROC EXPORT DATA=tmp(keep= &vlist1)
   OUTFILE="c:\downloads\ExcelFile.xls"
   DBMS=EXCEL
   REPLACE
   ;
   SHEET="var1_256";
RUN;

PROC EXPORT DATA=tmp (keep= &vlist2)
   OUTFILE="c:\downloads\ExcelFile.xls"
   DBMS=EXCEL
   REPLACE
   ;
   SHEET="var256_up";
RUN;
**********************log*************************
258  data tmp;
259  array var(500);
NOTE: The array var has the same name as a SAS-supplied or user-defined function.  Parentheses
      following this name are treated as array references and not function references.
260  do i = 1 to 500;
261     var(i)=i;
262  end;
263  keep var:;
264  run;

NOTE: The data set WORK.TMP has 1 observations and 500 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


265
266  proc print; run;

NOTE: There were 1 observations read from the data set WORK.TMP.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


267
268  proc sql noprint;
269  select name  into: vlist1 separated by ' '
270  from dictionary.columns
271  where upcase(libname)='WORK' and upcase(memname)='TMP'
272           and varnum<=256
273           ;
274  select name  into: vlist2 separated by ' '
275  from dictionary.columns
276  where upcase(libname)='WORK' and upcase(memname)='TMP'
277           and varnum>256
278           ;
279  quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.89 seconds
      cpu time            0.87 seconds


280
281  %put >>>> &vlist1 ;
>>>> var1 var2 var3 var4 var5 var6 var7 var8 var9 var10 var11 var12 var13 var14 var15 var16 var17
var18 var19 var20 var21 var22 var23 var24 var25 var26 var27 var28 var29 var30 var31 var32 var33
var34 var35 var36 var37 var38 var39 var40 var41 var42 var43 var44 var45 var46 var47 var48 var49
var50 var51 var52 var53 var54 var55 var56 var57 var58 var59 var60 var61 var62 var63 var64 var65
var66 var67 var68 var69 var70 var71 var72 var73 var74 var75 var76 var77 var78 var79 var80 var81
var82 var83 var84 var85 var86 var87 var88 var89 var90 var91 var92 var93 var94 var95 var96 var97
var98 var99 var100 var101 var102 var103 var104 var105 var106 var107 var108 var109 var110 var111
var112 var113 var114 var115 var116 var117 var118 var119 var120 var121 var122 var123 var124 var125
var126 var127 var128 var129 var130 var131 var132 var133 var134 var135 var136 var137 var138 var139
var140 var141 var142 var143 var144 var145 var146 var147 var148 var149 var150 var151 var152 var153
var154 var155 var156 var157 var158 var159 var160 var161 var162 var163 var164 var165 var166 var167
var168 var169 var170 var171 var172 var173 var174 var175 var176 var177 var178 var179 var180 var181
var182 var183 var184 var185 var186 var187 var188 var189 var190 var191 var192 var193 var194 var195
var196 var197 var198 var199 var200 var201 var202 var203 var204 var205 var206 var207 var208 var209
var210 var211 var212 var213 var214 var215 var216 var217 var218 var219 var220 var221 var222 var223
var224 var225 var226 var227 var228 var229 var230 var231 var232 var233 var234 var235 var236 var237
var238 var239 var240 var241 var242 var243 var244 var245 var246 var247 var248 var249 var250 var251
var252 var253 var254 var255 var256
282  %put >>>> &vlist2 ;
>>>> var257 var258 var259 var260 var261 var262 var263 var264 var265 var266 var267 var268 var269
var270 var271 var272 var273 var274 var275 var276 var277 var278 var279 var280 var281 var282 var283
var284 var285 var286 var287 var288 var289 var290 var291 var292 var293 var294 var295 var296 var297
var298 var299 var300 var301 var302 var303 var304 var305 var306 var307 var308 var309 var310 var311
var312 var313 var314 var315 var316 var317 var318 var319 var320 var321 var322 var323 var324 var325
var326 var327 var328 var329 var330 var331 var332 var333 var334 var335 var336 var337 var338 var339
var340 var341 var342 var343 var344 var345 var346 var347 var348 var349 var350 var351 var352 var353
var354 var355 var356 var357 var358 var359 var360 var361 var362 var363 var364 var365 var366 var367
var368 var369 var370 var371 var372 var373 var374 var375 var376 var377 var378 var379 var380 var381
var382 var383 var384 var385 var386 var387 var388 var389 var390 var391 var392 var393 var394 var395
var396 var397 var398 var399 var400 var401 var402 var403 var404 var405 var406 var407 var408 var409
var410 var411 var412 var413 var414 var415 var416 var417 var418 var419 var420 var421 var422 var423
var424 var425 var426 var427 var428 var429 var430 var431 var432 var433 var434 var435 var436 var437
var438 var439 var440 var441 var442 var443 var444 var445 var446 var447 var448 var449 var450 var451
var452 var453 var454 var455 var456 var457 var458 var459 var460 var461 var462 var463 var464 var465
var466 var467 var468 var469 var470 var471 var472 var473 var474 var475 var476 var477 var478 var479
var480 var481 var482 var483 var484 var485 var486 var487 var488 var489 var490 var491 var492 var493
var494 var495 var496 var497 var498 var499 var500
283
284
285  PROC EXPORT DATA=tmp(keep= &vlist1)
286     OUTFILE="c:\downloads\ExcelFile.xls"
287     DBMS=EXCEL
288     REPLACE
289     ;
290     SHEET="var1_256";
291  RUN;

NOTE: "var1_256" range/sheet was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.45 seconds
      cpu time            0.15 seconds


292
293  PROC EXPORT DATA=tmp (keep= &vlist2)
294     OUTFILE="c:\downloads\ExcelFile.xls"
295     DBMS=EXCEL
296     REPLACE
297     ;
298     SHEET="var256_up";
299  RUN;

NOTE: "var256_up" range/sheet was successfully created.
NOTE: PROCEDURE EXPORT used (Total process time):
      real time           0.42 seconds
      cpu time            0.14 seconds

EXCEL2007是不是会好一些？如果是的话装个office2007不就一切都解决了吗？O(∩_∩)O~个人意见，仅供参考~

淙淙_1st 发表于 2010-3-14 23:54
EXCEL2007是不是会好一些？如果是的话装个office2007不就一切都解决了吗？O(∩_∩)O~个人意见，仅供参考~

It seems to me that SAS proc export utility only do good for up to excel2003.

The ODS output options may bypass the limit.

导出到excel2007一个表搞定

• Open your ODBC administrator from your control panel:
      Start ► Settings ► Control Panel ► Administrative Tools
• Double-click Data Sources (ODBC) to open the ODBC Data Source Administrator dialog box.
• Select the System DSN tab and click AddSelect Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb).
• Click Finish, and the ODBC Microsoft Excel Setup dialog is displayed. In the Data Source Name field, type in the name that you want (for example, myexcel2007).
• Click the Select Workbook button and select the .xls or .xlsx file that you want. Note: If you want to write to the database, make sure the Read Only check box is not checked.
• libname myxls odbc
          required="Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
  dbq=C:\excelfiles\guangdong.xlsx";
  data myxls.baseball;
     set sashelp.class;
  run;

其他的你自己应该知道可以怎么改了吧

装个EXCEL2007试试看

这位同学，果然厉害

呵呵，谢谢各位的意见，本人采纳了5楼意见，安装office2007，结果还真导出来了，全部数据，感激之情无以言表，谢谢论坛，谢谢各位！