Export data from SAS into Microsoft Excel

There are a number of ways to export data from SAS into Microsoft Excel. However, very few allow for exporting into custom-formatted spreadsheets such as those demanded for specific reports, in which explicit attributes such as font sizes and column widths are required. Traditionally, Dynamic Data Exchange (DDE) was the only way to do this. Now there are newer methods for this which are considered superior…

Various methods are —

1. Proc Export / SAS Export wizard
2. Dynamic Data Exchange (DDE)
3. ODS CSV / HTML xls file route…create a .csv file and open with Excel
4. SAS to Excel via XML
5. Libname with xls
6. Using Display manager command Dexport to create a csv file

1. Proc Export / SAS Export wizard

2. Dynamic Data Exchange (DDE)

Export SAS datasets to Excel with DDE – 

3. ODS CSV / HTML xls file route…create a .csv file and open with Excel

4. SAS to Excel via XML

Export SAS datasets to Excel via XML –

5. Libname with xls

Export SAS datasets to Excel -

6. Using Display manager command Dexport to create a csv file….The Display manaager (DM Dexport) command invokes the External File Interface of SAS…

dm "dexport sashelp.class 'C:\class.csv' ";

dm "dexport sashelp.class 'C:\class.xls' ";

Interesting References….

Excellent Ways of Exporting SAS Data to Excel

Finding the nth business day of a given month and year

This sample shows a macro version and the data step version that can be used to find the nth business day of a given month and year.

This example uses the INTNX function to increment the date, time, or datetime value by a given interval or intervals.
http://support.sas.com/onlinedoc/913/getDoc/en/lrdict.hlp/a000212700.htm

This macro is a modified version of the Source: http://support.sas.com/kb/37/632.html

/* This code creates the macro variable DAY that is the nth day of the month. The macro variable is in the SAS date format

*/

%macro test1(checkday,month,year);

%global day;

%let date=%str(%'01)%upcase(%substr(&month,1,3))&year%str(%'d);

%put date=&date;

%let nthday=0; 

%let n=0;   

%do %until(&nthday=&checkday); 

/* create a variable whose value is the first day of the month*/  

%let begin_month=%sysfunc(intnx(month,&date,0,B)); 

/* increment the date by 1 day at a time*/  

%let day=%sysfunc(intnx(day,&begin_month,&n)); 

/* determine the day of the week*/  

%let weekday=%sysfunc(weekday(&day));

/* if the day of the week is not Saturday or Sunday then increment 

nthday plus 1*/ 

%if &weekday ne 7 and &weekday ne 1 %then %let nthday=%eval(&nthday+1);

%let n=%eval(&n+1);

%end;

/* checks the DAY macro variable by writing it to the log in the DATE9.

format*/

%put %sysfunc(putn(&day,date9.));

%mend; 

 

%test1(5,SEP,2010)   

 

%macro test2(checkday,month,year););

  %global day;

data test;  

  date=input(compress('01'||substr("&month",1,3)||&year),date9.);

  put date=;

  nthday=0;

  n=0; 

  do until(nthday=&checkday);

  begin_month=intnx('month',date,0,'B'); 

  day=intnx('day',begin_month,n);

  weekday=weekday(day);

  if weekday ne 7 and weekday ne 1 then nthday=nthday+1; 

  n+1; 

  end; 

  drop begin_month weekday date n; 

run; 

 

proc print;

  format day date9.; 

run; 

%mend; 

%test2(8,april,2010)

Patron segmentation on SAS VA