Hi everybody,
Hope you are well.
I am trying to get the name of the author of the excel file when i'm importing it.
I would like also to get the name of the last updator of the file.
Someone could help me ?
Thank's in advance for your help.
Mehdi
Hi,
SAS doesn't really give access to Excel properties, its really only interested in the data. You can of course remove this problem completely by putting the document properites into cells in the spreadsheet and importing that also:
I don't think there's a SAS option to get that information.
What I would do is try to get the file information from an operating system command feeding that into a SAS data set (filename pipe x... ) and use that as a control for the import, preserving the information you need (if you can get it from the system command).
You can run next code to see which attributes are avialble to SAS from OS.
Adapt the 2 macro variables to your need
%let path = ; /* full path to the file */
%let fname = ; /* file name with the extension */
%put NAME= &path.&fname; /* check result: path and name is it OK ? */*
data _null_;
*length infoname $30; /* unmark and adapt length if need */
rc=filename('tmp',"&path.&fname");
fid = fopen('tmp');
put fid=;
if fid then do;
infonum = foptnum(fid); put infonum=;
do i=1 to infonum;
infoname = foptname(fid,i);
fattr = finfo(fid,infoname);
put i= infoname = @33 fattr=;
end;
end;
run;
What SAS sees from the Excel spreadsheet is basically what you see when you save the spreadsheet to csv, give and take. The basic data in the cells.
Office-specific metadata are not in that stream, not when you use MS DAO or the modern xlsx engine. Either put that as data into the spreadsheet, or maybe you could coax Excel to reveal that information through clever calling from the commandline.
Or you try your hand at some VB-programming.
Author of excel file importing to SAS
inspired by
https://goo.gl/UbGFGd
https://communities.sas.com/t5/Base-SAS-Programming/author-of-excel-file-importing-to-SAS/m-p/338811
Extracting file meta data Owner, dates, author, title
* you can set properties but you need microsoft only tools to extract the meta data?
ods rtf
file="/mastat/calci/utilities/rjd/Prp.rtf"
Title="Table 1.1 Demographics for Target Protocol"
Author="Roger DeAngelis"
;
see microsoft only script below
HAVE
c:\utl\utl_toc_xls.sas
WANT
====
SOLUTION 1
NFOCNT=6
information for a Sequential File:
filename c:\utl\\utl_toc_xls.sas
RECFM V
LRECL 384
File Size (bytes) 20755
Last Modified 07Mar2017:05:35:28
Create Time 06Mar2017:08:23:43
SOLUTION 2
FILE_OWNER=workstation
FILE_NAME=utl_toc_xls.sas
FILE_DATE=2017-03-07
FILE_TIME=5:35:00
FILE_SIZE=20755
SOLUTION 3
File c:\utl\utl_toc_xls.sas
File size 20755 bytes
Created 06Mar2017:08:23:43
Last modified 07Mar2017:05:35:28
MS only solution
================
All the MS office meta data available,
not all meta data available for all MS applications.
Title
Subject
Author
Keywords
Comments
Template
Last author
Revision number
Application name
Last print date
Creation date
Last save time
Total editing time
Number of pages
Number of words
Number of characters
Security
Category
Format
Manager
Company
Number of bytes
Number of lines
Number of paragraphs
Number of slides
Number of notes
Number of hidden Slides
Number of multimedia clips
Hyperlink base
Number of characters (with spaces)
EXCEL
Title - Metadata Test
Subject - Excel Test Scripts
Author - Ken Myer
Keywords - testing, scripts
Comments - This is a sample spreadsheet used for testing purposes.
Template -
Last author - Ken Myer
Revision number -
Application name - Microsoft Excel
Creation date - 6/13/2006 8:40:17 PM
Last save time - 6/13/2006 9:07:15 AM
Security - 0
Category -
Format -
Manager -
Company - Microsoft Corporation
Hyperlink base -
On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")
For Each strProperty in objWorkbook.BuiltInDocumentProperties
Wscript.Echo strProperty.Name & " - " & strProperty.Value
Next
SOLUTION 1
filename x pipe 'dir /q c:\utl\utl_toc_xls.sas';
data want;
retain file_owner file_name;
infile x firstobs=6 truncover;
input @1 file_date mmddyy10.
@13 file_time time8.
file_size comma19.
file_owner $22.
file_name $32.;
list;
format file_date yymmdd10. file_time time8.;
put
file_owner= /
file_name = /
file_date = /
file_time = /
file_size = /
;
stop;
run;
filename x clear;
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0
6 03/07/2017 05:35 AM 20,755 backup-PC\backup utl_toc_xls.sas 77
SOLUTION 2
%utlopts;
%macro FileAttribs(filename);
%global rc fid fidc;
%global Bytes CreateDT ModifyDT;
%let rc=%sysfunc(filename(onefile,&filename));
%let fid=%sysfunc(fopen(&onefile));
%let Bytes=%sysfunc(finfo(&fid,File Size (bytes)));
%let CreateDT=%qsysfunc(finfo(&fid,Create Time));
%let ModifyDT=%qsysfunc(finfo(&fid,Last Modified));
%let fidc=%sysfunc(fclose(&fid));
%let rc=%sysfunc(filename(onefile));
%put File &filename ;
%put File size is &Bytes bytes;
%put Created &CreateDT;
%put Last modified &ModifyDT;
%mend FileAttribs;
%FileAttribs (c:\utl\utl_toc_xls.sas) ;
SOLUTION 3
File c:\utl\utl_toc_xls.sas
File size 20755 bytes
Created 06Mar2017:08:23:43
Last modified 07Mar2017:05:35:28
data _null_;
length opt $100 optval $100;
/* Allocate file */
rc=FILENAME('myfile',
'c:\utl\\utl_toc_xls.sas');
/* Open file */
fid=FOPEN('myfile');
/* Get number of information
items */
infocnt=FOPTNUM(fid);
put infocnt=;
/* Retrieve information items
and print to log */
put @1 'Information for a
Sequential File:';
do j=1 to infocnt;
opt=FOPTNAME(fid,j);
optval=FINFO(fid,upcase(opt));
put @1 opt @20 optval;
end;
/* Close the file */
rc=FCLOSE(fid);
/* Deallocate the file */
rc=FILENAME('myfile');
run;
data info;
length infoname infoval $60;
drop rc fid infonum i close;
rc=filename('abc','/xxxx/xxxxxxxx/xxx/xx.sas7bdat');
fid=fopen('abc');
infonum=foptnum(fid);
do i=1 to infonum;
infoname=foptname(fid,i);
infoval=finfo(fid,infoname);
output;
end;
close=fclose(fid);
run;
You can try something like the code below to read the information from files created using the SAS ODS Excel destination. You may need to change the code to read files created using Excel.
Vince DelGobbo
SAS R&D
filename xl zip '<path-to-file>\<filename>.xlsx' member='docProps/core.xml'; %symdel CREATEDBY MODIFIEDBY / nowarn; * For files created using the ODS Excel destination; data _null_; length CreatedBy ModifiedBy $256; infile xl; input @; if (index(_infile_, '<dc:creator>') ne 0) then do; input @ '<dc:creator>' CreatedBy; CreatedBy = substr(CreatedBy, 1, index(CreatedBy, '</dc:creator>')-1); call symputx('CREATEDBY', CreatedBy); end; else if (index(_infile_, '<cp:lastModifiedBy>') ne 0) then do; input @ '<cp:lastModifiedBy>' ModifiedBy; ModifiedBy = substr(ModifiedBy, 1, index(ModifiedBy, '</cp:lastModifiedBy>')-1); call symputx('MODIFIEDBY', ModifiedBy); end; run; %put _GLOBAL_;
Here's a different approach that should work for files created using the SAS ODS Excel destination and also those created by Excel.
Vince DelGobbo
SAS R&D
filename xl zip '<path-to-file>\<filename>.xlsx' member='docProps/core.xml'; filename tempxml temp; data _null_; infile xl; file tempxml; input; put _infile_; run; filename tempmap temp; libname tempxml xmlv2 automap=replace xmlmap=tempmap; %symdel CREATEDBY MODIFIEDBY / nowarn; data _null_; set tempxml.coreProperties; call symputx('CREATEDBY', creator); call symputx('MODIFIEDBY', lastModifiedBy); run; %put _GLOBAL_;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.