HOW DO I TRANSPOSE MY SAS DATA WHEN WRITING TO EXCEL?

* The following code opens Excel and the sample spreadsheet you wish to  populate with 

SAS observations ;



options noxsync noxwait ;



filename cmds dde 'excel|system' ;



x 'c:\progra~1\micros~2\office\excel.exe c:\intheknow\Issue8_HT2.xls' ;



* The sleep function will allow Excel enough time to open ;



data _null_ ;

  x=sleep(5) ;  

run ;



* A DDE link is established to Microsoft Excel, a SHEET called Class2A, 

rows 4 through 10 and columns 2 through 20 ;



filename classx dde "excel|Class2A!R4C2:R10C20" ;   



* Use Proc Transpose to rotate the data so its in the correct orientation for Excel. ;



* Note: The array has been defined with a type of _character_. A warning is generated as 

the var statement within Proc Transpose contains both character and numeric variables. 

If the var statement contains all numeric variables then change the type 

in the array to _numeric_. ;



proc transpose data=sashelp.class out=work.class ; 	

   var name age sex height weight ;

   where sex ='M' ;

run ;



* Add in blank rows using the put statement ;



data _null_ ;

   file classx ;

   set work.class ;

   array char [*] _character_;

   if _name_ in ('Sex' 'Height') then 

      put ;

   do i=2 to dim(char) ;

      put char[i] @ ;

   end ;

   put ;

run ;



* Then Save the spreadsheet, Close the spreadsheet and Quit Excel ;



data _null_;

      file cmds;

      put '[SAVE()]';

      put '[CLOSE()]';

      put '[QUIT()]';

      stop;

run ;