topic Re: Export free text to Excel in SAS Programming

Export free text to Excel

Chris_LK_87 — Wed, 22 Sep 2021 14:45:33 GMT

Hello,

My dataset look like this (see below) and is exported to excel with ods. I would like to add free text in a different sheet in the same excel file. The free text should look like this (like a log):

2021-09-22: Changed variabelname for City

2021-09-20: Made uppercase fo City

2021-09-18: Created dataset

2021-09-15: Created a mean

I have tried different ways but have not found any good solution to export free text to excel.

data have;
;
input City$ month$ year2017 year2018 year2019 mean2017_2019;
datalines;
London Jan 10 15 20 15
Rom Jan 20 25 30 25
Paris Jan 20 10 10 13.33
Berlin Jan 50 60 70 60
London Feb 20 30 40 30
Rom Feb 40 50 60 50
Paris Feb 40 20 20 26.67
Berlin Feb 100 90 50 80
;
run;

ODS EXCEL FILE='c:\file-path\Cities.xlsx'
options (sheet_interval='none' suppress_bylines="off" sheet_name="City);

PROC REPORT data=have;
run;

ODS EXCEL CLOSE;

Re: Export free text to Excel

PaigeMiller — Wed, 22 Sep 2021 14:55:12 GMT

Put your "free text" in a SAS data set. Then:

ods excel file='c:\file-path\Cities.xlsx' options (sheet_interval='none' suppress_bylines="off" 
    sheet_name="City");

proc report data=have;
run;

ods excel options(sheet_name='Free Text');
proc print data=free_text;
run;

ODS EXCEL CLOSE;

Re: Export free text to Excel

Reeza — Wed, 22 Sep 2021 14:52:57 GMT

Have you tried PROC ODSTEXT?
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/odsproc/p1tfjsx3ezqhacn1gqqsxqii7lpv.htm

Or ODS TEXT?
https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/odsproc/p1tfjsx3ezqhacn1gqqsxqii7lpv.htm

Or save that information to a data set (single variable) and use PROC REPORT with the NOHEADER option?
Add No Header to your own example.....

Or footnote statements if you want them at the bottom of the table?

I would suggest keeping them in a data set so you can just add to them in the future and use the PROC REPORT with the NOHEADER option to print them to the file.

Re: Export free text to Excel

Chris_LK_87 — Thu, 23 Sep 2021 07:53:03 GMT

Thanks!

This might be a silly question, but howe do add "free text " in a SAS dataset?

I have tried:

Data logg;
file print;
put 'Hello world';
run;

Re: Export free text to Excel

Kurt_Bremser — Thu, 23 Sep 2021 08:04:32 GMT

Datasets contain variables, variables contain data.

To add data to a dataset, add a variable and give it a value:

data want;
input free_text $80.;
datalines;
Free Text
Another Free Text
;

Re: Export free text to Excel

Chris_LK_87 — Thu, 23 Sep 2021 14:14:59 GMT

Thank!