CODEWITHSUNDEEP
macrossassas-macrodde
user3424954
user3424954

Reputation: 75

How do I get Sas Dataset Headers to export to excel sheet via DDE

So I am trying to write a macro which exports my sas dataset to an existing excel sheet. I am using the following code which I found from one of the SUGI papers. The problem is that the following code does the job successfully, but it just can not copy paste the headers(variable names) to the excel and just pastes all the observations. How do I get my code to also paste the headers(variable names) to the excel

%MACRO EXCELOUT(SDS=,XLSSHT=,XLSF=, ROW=,COL= ) ;

PROC CONTENTS DATA=&SDS NOPRINT OUT=CNT ;
RUN;
PROC SORT DATA=CNT ;
BY VARNUM ;
RUN;

PROC SQL NOPRINT;
SELECT NAME
INTO: VARS SEPARATED BY ' '
FROM CNT ;
SELECT COUNT(DISTINCT NAME)
INTO: COLS SEPARATED BY ' '
FROM CNT ;
SELECT NOBS
INTO: ROWS
FROM CNT
WHERE VARNUM = 1;
QUIT;

OPTIONS NOXWAIT NOXSYNC ;
X "&XLSF" ;

DATA _NULL_ ;
X=SLEEP(5);
RUN ;

FILENAME TEMP DDE "EXCEL|&XLSSHT.!R&ROW.C&COL.:R%TRIM(%EVAL(&ROWS+&ROW1))C%TRIM(%EVAL(&COLS+&COL)" ;

DATA _NULL_ ;
SET &SDS ;
FILE TEMP ;
PUT &VARS ;
RUN ;

FILENAME CMDS DDE 'EXCEL|SYSTEM' ;
DATA _NULL_ ;
FILE CMDS ;
PUT '[SAVE()]' ;
PUT '[QUIT()]' ;
RUN ;

%MEND EXCELOUT ;

Upvotes: 1

Views: 645

Answers (1)

Tom
Tom

Reputation: 51621

Just add a little more logic to the step that writes the data to have it write the names also.

data _null_ ;
  file temp ;
  if _n_=1 then do;
    do _n_=1 to &cols ;
      set cnt(keep=name rename=(name=__name__)) ;
      put __name__ @;
    end;
    put;
  end;
  set &sds ;
  put &vars ;
run ;

Your code to get the variable names and count the rows and columns could be simpler also.

proc sql noprint;
 select nobs
      , name
   into :rows trimmed
      , :vars separated by ' '
   from cnt
 ;
 %let cols = &sqlobs ;
quit;

Your FILENAME statement does not need %TRIM(), and was missing the + needed to add one row for the header. Made it look like it was referencing &row1 instead of &row. 

filename temp dde "excel|&xlssht.!r&row.c&col.:r%eval(&rows+&row+1)c%eval(&cols+&col)" ;

Upvotes: 1

Related Questions