DATA Step, Macro, Functions and more

author of excel file importing to SAS

Reply
Occasional Contributor
Posts: 6

author of excel file importing to SAS

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

Super User
Super User
Posts: 7,942

Re: author of excel file importing to SAS

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:

https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_winother/display-document-prop...

 

 

PROC Star
Posts: 307

Re: author of excel file importing to SAS

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).

Trusted Advisor
Posts: 1,555

Re: author of excel file importing to SAS

[ Edited ]
Posted in reply to collinelliot

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;

Super User
Posts: 7,768

Re: author of excel file importing to SAS

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 505

Re: author of excel file importing to SAS

Posted in reply to KurtBremser
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;
SAS Super FREQ
Posts: 304

Re: author of excel file importing to SAS

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_;
SAS Super FREQ
Posts: 304

Re: author of excel file importing to SAS

Posted in reply to Vince_SAS

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_;
Ask a Question
Discussion stats
  • 7 replies
  • 403 views
  • 6 likes
  • 7 in conversation