SAS attach excel last modified date to imported table

Question

I was trying to import excel to sas grid and add the last modified timestamp for the file into the table.

Referred to the sas support I was able to retrieve the last modified time. But I am having trouble with add the time as a new column into the table.

I got this error:

SYMBOLGEN: Macro Variable LASTMODIFIED resolves to 09May2023:16:33:19
NOTE: LINE generated by the macro variable "LASTMODIFIED"
282   09May2023:16:33:19
      ----------
       22
       76

ERROR 22-322: Syntax error, expecting one of the following: !, !!....
ERROR 76-322: Syntax error, statement will be ignored.

Here is the code I tried:

%macro FileAttribs(filename);           
  %local rc fid fidc ModifyDT; 
  %let rc=%sysfunc(filename(onefile,&filename)); 
  %let fid=%sysfunc(fopen(&onefile));
     %if &fid ne 0 %then %do;      
  %let ModifyDT=%sysfunc(finfo(&fid,Last Modified));    
  %let fidc=%sysfunc(fclose(&fid));    
  %let rc=%sysfunc(filename(onefile));    
  %put NOTE- Last modified &modifydt;

  proc sql;
  create table work.final as
  select *, &modifydt. as lastmodified
  from work.tb; --the imported table from excel

     %end;
        %else %put &filename could not be open.;
%mend FileAttribs;

Also tried to cast the variable to different datatype but also failed.

This code will only work on Unix as the FINFO() function on Windows is not able to get the 'Last Modified' metadata about a file. — Tom
– Tom, Commented May 11, 2023 at 4:58

Reeza · Accepted Answer · 2023-05-10 23:02:28Z

1

The macro variable is a text string which confuses SQL. Add double quotes and the letters DT to tell SAS you want this to be interpreted as a datetime. Double quotes are for the macro variable to resolve. More information on date constants can be found here.

"&modifydt."dt as lastmodified format=datetime20.

answered May 10, 2023 at 23:02

Reeza

21.4k4 gold badges26 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

yang chaoyi Over a year ago

Thank you it worked! Can’t believe it takes me 2 hours to figure it out.

Tom · Accepted Answer · 2023-05-11 04:57:44Z

Why did you go to all the trouble to put the value into a macro variable if you needed it in a dataset? Instead of using %SYSFUNC() so you can call SAS functions with macro code just use a data step so you can call the functions directly.

%macro FileAttribs(filename);   
%local found;
%let found=0;

filename onefile "&filename" ;
data lastmodified;
  fid = fopen('onefile');
  if fid then do;
    lastmodified = input(finfo(fid,'Last Modified'),datetime19.);
    call symputx('found','1');
    put 'NOTE: FILENAME=' "'&filename'" lastmodified= ;
    fid=fclose(fid);
  end;
  else put 'WARNING: FILENAME=' "'&filename'" ' not found.';
  format lastmodified datetime19.;
  drop fid ;
run;
filename onefile ;

%if &found %then %do;
data work.final;
  set work.tb ;
  if _n_=1 then set lastmodified;
run;
%end;
%mend FileAttribs;

Note: This code will only work on Unix as the FINFO() function on Windows is not able to get the 'Last Modified' metadata about a file.

Collectives™ on Stack Overflow

SAS attach excel last modified date to imported table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related