How to change the column headers of a sas dataset into an observation?

Question

I have created a sas code which generates many sas datasets. Now I want to append all of them to a single excel file . So first I want to convert all the column headers of sas datasets as first observation. Then leave space between these datasets (adding a blank observation). How can we do it?

Answer 1

one way to do this would be to use dictionary.columns

proc sql;
create table Attribute as
select * from dictionary.columns;

Read through the table and check what attributes you are interested in. For your case you might be interested in the column "NAME" <- consist of the name of all columns.

Modify the table by adding where statement to the proc sql based on the identity of the column ( from which library / what type of file / name of file) e.g. where upcase(libname)= "WORK"

data attribute;
array column [ n ] $ length ;
do i=1 to n;
set attribute ( keep = name) ;
column [ i ] = name ;
end;
run;

Then I would proceed with data step. You could use macro variable to store the value of column's names by select variable into : but anyhow you still need to hardcode the size for the array n or any other method that store value into one observation . Also remember define the length and the type of array accordingly. You can give name to the variable in the result dataset Attribute by adding var1-varnafter the length at array statement.

For simplicity I use set statement to read observation one and one and store the value of column NAME, which is the official column name derived when using dictionary.columns into the array

Note that creating a non-temporary array would create variable(s) .

Add if you want to add the blank,

  data younameit ;
  merge attribute attribute(firstobs=2 keep=name rename=(name=_name));
  output;
  if name ne _name then do;
  call missing(of _all_);
  output;
  end;
  run;

As two datasets start with different observation and column names do not duplicate within one dataset, the next row of a valid observation ( derived from the first output statement in the resulting dataset would be empty due to call missing ( of _all_ ) ; output;

Answer 2

Sounds like you just want to combine the datasets and write the results to the Excel file. Do you really need the extra empty row?

libname out xlsx 'myfile.xlsx';
data out.report ;
  set ds1 ds2 ...;
run;

Answer 3

Ensure that all your columns are character (or numeric, substitute numeric), then in your data step use:

array names{*} _character_;
do i=1 to dim(names);
    call label(names{i}, names{i}); 
end;
output;