PUTTING A HEADER PAGE ON EACH EXCEL FILE

[CathyVI]

Hi,

I found this code online and tried to replicate it but I keep getting an error. The code is used to create an header on each excel file. The error said the physical path does not exist but I am not sure of the path shown. Can someone direct me on how to make this works pls.  Below is the code and the error log. 

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

72

73

74 options nocenter msglevel=i ls=132 mprint;

75

76

77 * WE ARE NOW PUTTING A HEADER PAGE ON EACH EXCEL FILE TO DOCUMENT THE PROGRAM SOURCE ;

78 data header_page;

79 length Information $ 150 ;

80 if _n_=1 then do;

81 Information = "RUN DATE: " || put(today(), yymmddd10.) || " at &systime" ; output;

82 Information = "FILE NAME: " || "%substr(&SYSPROCESSNAME,9)"; output;

83 Information = "RUN BY: " || "&sysuserid" ; output;

84 Information = "*********************************************************" ; output;

85 end;

86 infile "%substr(&SYSPROCESSNAME,9)" truncover;

87 input Information $150. ;

88 if compress(upcase(information)) =: "*MODIFICATIONS:" then stop;

89 output;

90 run;

ERROR: Physical file does not exist, /usr/local/biserver/Lev1/SASApp/erver.

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.HEADER_PAGE may be incomplete. When this step was stopped there were 0 observations and 1 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

user cpu time 0.00 seconds

system cpu time 0.00 seconds

memory 496.71k

OS Memory 26784.00k

Timestamp 08/30/2022 05:44:35 PM

Step Count 18 Switch Count 2

Page Faults 1

Page Reclaims 122

Page Swaps 0

Voluntary Context Switches 12

Involuntary Context Switches 0

Block Input Operations 264

Block Output Operations 16

91

92 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

105

[PaigeMiller]

I think this line causes the problem

infile "%substr(&SYSPROCESSNAME,9)" truncover;

This resolves to

infile "/usr/local/biserver/Lev1/SASApp/erver" truncover;

which is a file that does not exist. You need to specify a file that does exist. (Also, it's not clear to me why you are doing a %substr on this file name).

The usual advice is first to get this to work without macro variables and without macro functions, by hardcoding a particular file name. Show us that code without macro variables and without macro functions, that works.