CODEWITHSUNDEEP
sassas-macro
Ezio_Auditore
Ezio_Auditore

Reputation: 43

How to make first row as the header while importing an excel (xlsm) file into SAS

I have a excel sheet (Xlsm) that I wanted to export , issue is that the first row is supposed to be the header. How do I replace the header with the first row while importing. Please not that I cannot make changes to the base file.

Data that I have

         A         B         C 
1      Name 1   Name 2     Name 3
2        2        4          66
3        3        5          6

Data that I need.

         Name 1        Name 2        Name 3
1             2          4               66   
2             3          5               6

I have used DATAROW but its not working.

Upvotes: 0

Views: 104

Answers (2)

Richard
Richard

Reputation: 27498

To CREATE an Excel file from a SAS data set, using the values in the first row of the data set as the column headers in the Excel file you will need to do two things:

  1. Code generate a LABEL statement based on the data set
  2. Use the LABEL option when you PROC EXPORT

Example:

data goofy ;
  length a b c $8;
  input a & b & c & ;
  datalines ;
Name 1   Name 2     Name 3
   2        4          6
   3        5          7
;

* compute labels for label statement ;
data _null_ ;
  set goofy (obs=1) ;
  length _name_ $32 ;
  length labels $32767 ;
  do _n_ = 1 by 1 ;
    call vnext(_name_) ;
    if _name_ = '_name_' then leave ;
    labels = catx(' ', labels, catx('=', _name_, quote(trim(vvaluex(_name_))))) ;
  end ;
  call symputx('labels',labels) ;
  put 'NOTE: Computed ' labels= ;
run ;

* pre delete the output so we get a fresh Excel file;
* If not done you might be replacing a worksheet in an existing workbook ;
filename xlout "want.xlsx";
%let rc = %sysfunc(fdelete(xlout)) ;

proc export dbms=xlsx label data=goofy(firstobs=2) replace outfile=xlout ;
  label &labels;
run ;

enter image description here

enter image description here

Upvotes: 0

Tom
Tom

Reputation: 51566

PROC IMPORT always takes the names from the first row of data (unless you tell it not to by using GETNAMES=NO; statement).

DATAROW option has no impact on where it looks for the names.

If the names are on the FIRST row then that is the default for where to find them so no additional options are needed.

If the names are on the second row then use the RANGE setting instead. Set the range to start in the first column and second row.

Try this example:

filename xlsx temp;
proc export data=sashelp.class file=xlsx dbms=xlsx replace; 
run;
proc import file=xlsx dbms=xlsx out=want replace;
  range='$A2:';
run;

Here is a picture of what the XLSX file will look like: enter image description here

And here is the picture of the SAS dataset that results when told to read starting with the second row.

enter image description here

Upvotes: 1

Related Questions