export .xlsx with original headings

Reply
Contributor
Posts: 36

export .xlsx with original headings

Hi,

I have an input .csv file which looks like

Record #,    Reading Grade,   Math Grade

1, 20,  30

2, 33,  40

I imported this data and get the following dataset,

data1:

Record__         Reading_Grade       Math_Grade

1                             20                           30

2                             33                           40

I did some calculation and created one new variable 'Total_Grade',

data2:

Record__         Reading_Grade       Math_Grade   Total_Grade

1                             20                           30                    50

2                             33                           40                    73

I need to export the new data to a .xlsx file which should have the same headings as the original file not the variable names that were automatically changed by SAS. It should look like the following.

Record #    Reading Grade   Math Grade     Total Grade

1                      20                     30                      50

2                      33                     40                      73

I was thinking of first only reading in the heading of the original file and -proc export- it into the .xlsx file, and then using another -proc export-   to output only the result data with 'putname = no' and WITHOUT 'REPLACE' option to the same .xlsx file. But it looked like the second -proc export- still replaced the first one. Does anybody know the reason? Or do you have any other suggestions to implement this? Thank you very much.

Super User
Posts: 19,810

Re: export .xlsx with original headings

SAS doesn't allow those types of names, but when you import the original file it should have the correct names as LABELS.

To export with labels, try using the LABELS option in proc export thought that may depend on your destination. Otherwise, consider exporting to a CSV and importing to Excel, or using Tagsets.ExcelXP or HTML.

Super User
Super User
Posts: 7,050

Re: export .xlsx with original headings

If you used PROC IMPORT to read from the XLSX file then the original column headers should be preserved as the variable labels.  You can then add the LABEL option to the PROC EXPORT step to write the labels instead of the variable names as the column headers.

If you are reading/writing CSV files then the names are not preserved as labels, but you can read them in anyway and use them to generate a LABEL statement so that you can again use the LABEL statement to generate the new output using the labels instead of the names.

filename csv_in temp;

filename csv_out temp;

data _null_;

  file csv_in;

  input;

  put _infile_;

cards4;

Record #,    Reading Grade,   Math Grade

1, 20,  30

2, 33,  40

;;;;

proc import datafile=csv_in out=have dbms=dlm replace;

  getnames=no;

  datarow=2;

  delimiter=',';

run;

data labels ;

  infile csv_in dsd dlm=',' truncover ;

  length name $32 label $256 ;

  name = cats('VAR',_n_);

  input label @@;

  if label = ' ' then stop;

run;

proc sql noprint ;

  select catx('=',name,quote(trim(label)))

    into :labels separated by ' '

    from labels

  ;

quit;

data want ;

set have ;

label &labels;

run;

proc export outfile=csv_out data=want dbms=dlm replace label;

  delimiter=',';

run;

data _null_;

infile csv_out;

input;

put _infile_;

run;

Ask a Question
Discussion stats
  • 2 replies
  • 214 views
  • 0 likes
  • 3 in conversation