topic Re: SAS File in Use in SAS Programming

SAS File in Use

saikiran_nemani — Wed, 12 Jun 2019 17:29:20 GMT

The Error is saying that File is in open

Re: SAS File in Use

Kurt_Bremser — Wed, 12 Jun 2019 18:49:53 GMT

Then go looking for the process that accesses it, and terminate, or do whatever releases the handle.

Re: SAS File in Use

Reeza — Wed, 12 Jun 2019 19:10:05 GMT

@saikiran_nemani wrote:

The Error is saying that File is in open

99% of the time SAS is correct and the file is open. So you need to close it. You didn't really give us enough information to help point you in any direction, beyond find the file that is open and closing it. If this file is shared, anyone using the file could create the issue.

Re: SAS File in Use

ballardw — Wed, 12 Jun 2019 20:07:26 GMT

If you have view of a SAS data set open, that is "in use".

If you have an external file, such as spreadsheet, open in the spreadsheet program it is "in use".

In some cases, if you have a file named on a libname or filename statement it might be "in use".

Re: SAS File in Use

saikiran_nemani — Thu, 13 Jun 2019 01:02:23 GMT

The Error Part

ERROR: File is in use, T:\first_time_install_a_12JUN19_v1.log.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE PRINTTO used (Total process time):
      real time           0.07 seconds
      cpu time            0.01 seconds

The Program:

option mlogic symbolgen mprint;

%global start_dat;
%global end_dat;
%global fend_dat;
%global month_text;
%global year_text;
/*DHATTON NOVEMBER 2007 
- LOGIC USED TO DETERMINE THE REPORTING FREQUENCY AND INVOKE REPORTS FOR THE FAULTS AFTER INSTALL REPORTS*/

/*
DHATTON NOV2007 - INCLUDES FAULTS AFTER INSTALL GEOGRAPHICAL DRILLDOWN
DHATTON NOV2007 - INCLUDES FAULTS AFTER INSTALL MANAGERIAL DRILLDOWN
DHATTON DEC2007 - PERFORMANCE DAMAGES 30 DAY REPORT INCLUDED IN 30 DAY REPORTING LOGIC TO AVOID REPEAT OF 30 DAY GRAB FI CALL
DHATTON AUG2008 - ORDER OF EVENTS CHANGED TO RUN PREVIOUS MONTH INSTANCE BEFORE MTD EARLY MONTH INSTANCE
DHATTON 27MAY2009 - Include to FTI main drilldown changed to v1_5 following R Cooke changes for scorecard extract changes 
DHATTON 22JAN2010 - move grab_fi macro compile to here as failing to be compiled in the autoexec due to a bug
DHATTON 01FEB2010 - move grab_fi macro compile moved back to macro programs central following bug fix
ABAXTER 19FEB2010 - Virginise KPIS
DHATTON 20JUL2010 - Compile of grab_fi incremented to v3_3
Alan Joel 31Jan2011 Compile of grab_fi incremented to v3_5
*/

/**

	Author:		James Walsh
	Version:	1.8_obiafm
	Updated:	7th December 2010

	Updated program to output data to SQL Server

**/
/*
1)change the folder name in the script T:\Dipti\Marvin_check\FTI_main_drilldown_v1_9_obiafm_v2_apr.sas
on line no 527 %let outfile=%str(T:\TRAC\Neil\Upload\Fault_Installs\First_Time_Install_Geographical_report\2018_8);
e.g:for october month change the 2018_8 to 2018_10
on line no 1405 and %let outfile1=%str(T:\TRAC\Neil\Upload\Fault_Installs\First_Time_Install_Field_Mgmt\2018_8);
e.g:for october month change the 2018_8 to 2018_10
2)change the date on line no 385 e.g:if we are running it for the month of october change the date to '08NOV2018'd
as we run this code on 8th of current for to get the output for the previous month.
/** JW 07.12.2010 - flag testing **/

%let afmsc_testing = N;
%syslput afmsc_testing = &afmsc_testing;

/* determine the reporting frequency */
/* can be used across mtd faults after install reports*/

/*compile back to macro programs central 01FEB2010*/

%include 'T:\MI_TECH\MACROS\grab_fi_v7_2.sas';

/*SET REFERENCE TO REPORTING CODESETS*/
%include 'T:\Dipti\Marvin_check\FTI_main_drilldown_v1_9_obiafm_v2_monthend.sas';


%global start_dat;
%global end_dat;
%global fend_dat;
%global month_text;
%global year_text;


%macro RUN_DECIDER_FAI (my_today_overide=,run_early_overide=,run_main_overide=,run_30_overide=,manual_run=);
/*decide whether it is a month end run or a weekly mtd run*/
Data dates;

	my_today=&my_today_overide.;
/* to overide and rerun a previous months report uncomment the following and 
	set to the rundate where month end run would occur first for 7 day report (8th day)*/
/* 	my_today = '08SEP2010'd;*/
	my_monthday=day(my_today);
	my_weekday=weekday(my_today);

/*run main report decider*/
	If my_Weekday in (2 3 4 5 6) then	/*set to run each weekday (sasdayofweek )*/
	/*If my_Weekday = 6 then		*/	/*set to run on a thursday (sasdayofweek = 5)*/
		Run_Main_Report = "Y";
	else
		Run_Main_Report = "N";

/*run early month report decider*/
	If my_monthday in (3 4 5 6 7 8) then
		Run_early_Report = "Y";
	ELSE if my_monthday = 9 and my_weekday = 2 	then
		Run_early_Report = "Y";
	ELSE if my_monthday = 10 and my_weekday = 2	then
		Run_early_Report = "Y";
	else
		Run_early_Report = "N";

/*main and early month reporting period decider*/

	If my_monthday le 8 then 
		do;
			Run_main_for_Prevmon = "Y";
			Run_early_for_Prevmon = "N";
		end;
	ELSE if my_monthday = 9 and my_weekday = 2 	then
		do;
			Run_main_for_Prevmon = "Y";
			Run_early_for_Prevmon = "N";
		end;
	ELSE if my_monthday = 10 and my_weekday = 2	then
		do;
			Run_main_for_Prevmon = "Y";
			Run_early_for_Prevmon = "N";
		end;
	else
		do;
			Run_main_for_Prevmon = "N";
			Run_early_for_Prevmon = "N";
		end;
	
/*run 30 day fai report decider*/
	If my_monthday le 1 then
		Run_30day_Report = "Y";
	ELSE if my_monthday = 2 and my_weekday = 2 	then
		Run_30day_Report = "Y";
	ELSE if my_monthday = 3 and my_weekday = 2	then
		Run_30day_Report = "Y";
	else
		Run_30day_Report = "N";

call symput('YsNMain',Run_Main_Report);/*determines if main report should be run*/
call symput('YsNEarly',Run_early_Report);/*determines if early month version should be run*/
call symput('YsNMPrev',Run_main_for_Prevmon);/*determines time of month for grab fidates in above reports*/
call symput('YsNEPrev',Run_early_for_Prevmon);/*determines time of month for grab fidates in above reports*/
call symput('YsN30day',Run_30day_Report);
call symput('my_today',my_today);

run;



/* set up the parameters for the monthly report based on the my_today above*/
%Macro Run_Early_report(Run_early_Report=,Run_main_for_Prevmon=,Run_early_for_Prevmon=, my_today= );
%if &YsNEarly. = Y %then
%do;
	Data Date_Early;
	Run_early_for_Prevmon = "&Run_early_for_Prevmon.";
	Run_main_for_Prevmon = "&Run_main_for_Prevmon.";
	my_today = &my_today.;
	my_monthday = day(&my_today.);
	my_weekday = weekday(&my_today.);

	if Run_early_for_prevmon eq "N" then
		do;
			Start = intnx('Month',&my_today.,0);
				end =  &my_today. -8 ;
				fend = &my_today. ;
		end;
	else if Run_early_for_prevmon eq 'Y' then
		do;
				Start = intnx('Month',&my_today.,-1);
				end = intnx('Month',&my_today.,0)-1;
				fend = intnx('Month',&my_today.,0)-1+8;	
		end;
	
	Month_Num = Month(Start); 
	Year_Num = Year(Start);
	Month_Text = trim(left(put(Month_Num, z2.)));
	Year_Text = trim(left(put(Year_Num, z4.)));
	Month_Name = trim(left(put(Start, monname9.)));
	Short_Year = trim(left(substr(Year_Text,3,2)));;
	monthly=0;
	monthtext = Trim(left(Month_Name))||trim(left(Short_Year));
	repsmonth = Month_Num;
	monyear = put(start,MONYY.);

	call symput('Run_main_for_Prevmon',Run_main_for_Prevmon);
	call symput('Run_early_for_Prevmon',Run_early_for_Prevmon);
	call symput('Start_dat',Start);
	call symput('End_dat',End);
	call symput('Fend_dat',Fend);
	call symput('monthly',monthly);
	call symput('month_text',month_text);
	call symput('month_num',month_num);
	call symput('monthtext',monthtext);
	call symput('month_name',month_name);
	call symput('year_text',year_text);
	call symput('short_year',short_year);
	call symput('repsmonth',repsmonth);
	call symput('monyear',monyear);
	format start end fend date9.;
	run;

%put &Start_dat;
%put &End_dat;
%put &Fend_dat;
%put &monthly.;
%put &month_text.;
%put &month_num.;
%put &monthtext.;
%put &month_name.;
%put &year_text.;
%put &repsmonth.;
%put &short_year;
%put &monyear;

/*RUN THE EARLY MONTH REPORT THROUGH*/

%early_month_grab_and_drill;

%end;
%Mend run_Early_report;





/* set up the parameters for the monthly report based on the my_today above*/
%Macro Run_Main_report(Run_Main_Report=,Run_main_for_Prevmon=,Run_early_for_Prevmon=, my_today= );
%if &YsNMain. = Y %then
%do;
	Data Date_Main;
	Run_early_for_Prevmon = "&Run_early_for_Prevmon.";
	Run_main_for_Prevmon = "&Run_main_for_Prevmon.";
	my_today = &my_today.;
	my_monthday = day(&my_today.);
	my_weekday = weekday(&my_today.);

	if Run_main_for_prevmon eq "N" then
		do;
			Start = intnx('Month',&my_today.,0);
				end =  &my_today. -8 ;
				fend = &my_today. ;
		end;
	else if Run_main_for_prevmon eq 'Y' then
		do;
				Start = intnx('Month',&my_today.,-1);
				end = intnx('Month',&my_today.,0)-1;
				fend = intnx('Month',&my_today.,0)-1+8;	
		end;
	
	Month_Num = Month(Start); 
	Year_Num = Year(Start);
	Month_Text = trim(left(put(Month_Num, z2.)));
	Year_Text = trim(left(put(Year_Num, z4.)));
	Month_Name = trim(left(put(Start, monname9.)));
	Short_Year = trim(left(substr(Year_Text,3,2)));;
	monthly=0;
	monthtext = Trim(left(Month_Name))||trim(left(Short_Year));
	repsmonth = Month_Num;
	monyear = put(start,MONYY.);

	call symput('Run_main_for_Prevmon',Run_main_for_Prevmon);
	call symput('Run_early_for_Prevmon',Run_early_for_Prevmon);
	call symput('Start_dat',Start);
	call symput('End_dat',End);
	call symput('Fend_dat',Fend);
	call symput('monthly',monthly);
	call symput('month_text',month_text);
	call symput('month_num',month_num);
	call symput('monthtext',monthtext);
	call symput('month_name',month_name);
	call symput('year_text',year_text);
	call symput('short_year',short_year);
	call symput('repsmonth',repsmonth);
	call symput('monyear',monyear);
	format start end fend date9.;
	run;

%put &Start_dat;
%put &End_dat;
%put &Fend_dat;
%put &monthly.;
%put &month_text.;
%put &month_num.;
%put &monthtext.;
%put &month_name.;
%put &year_text.;
%put &repsmonth.;
%put &short_year;
%put &monyear;

/*RUN THE MAIN REPORT THROUGH*/

%main_month_grab_and_drill;		

%end;
%Mend run_Main_report;



/* set up the parameters for the monthly report based on the my_today above*/
%Macro Run_30day_report(Run_30day_Report=, my_today= );
%if &YsN30day. = Y %then
%do;
	Data Date_30;
	Run_30day_Report = "&Run_30day_Report.";
	my_today = &my_today.;
	my_monthday = day(&my_today.);
	my_weekday = weekday(&my_today.);

				Start = intnx('Month',&my_today.,-2);
				end = intnx('Month',&my_today.,-1)-1;
				fend = intnx('Month',&my_today.,-1)-1+30;	
		
	Month_Num = Month(Start); 
	Year_Num = Year(Start);
	Month_Text = trim(left(put(Month_Num, z2.)));
	Year_Text = trim(left(put(Year_Num, z4.)));
	Month_Name = trim(left(put(Start, monname9.)));
	Short_Year = trim(left(substr(Year_Text,3,2)));;
	monthly=0;
	monthtext = Trim(left(Month_Name))||trim(left(Short_Year));
	repsmonth = Month_Num;
	monyear = put(start,MONYY.);

	call symput('Run_30day_Report',Run_30day_Report);
	call symput('Start_dat',Start);
	call symput('End_dat',End);
	call symput('Fend_dat',Fend);
	call symput('monthly',monthly);
	call symput('month_text',month_text);
	call symput('month_num',month_num);
	call symput('monthtext',monthtext);
	call symput('month_name',month_name);
	call symput('year_text',year_text);
	call symput('short_year',short_year);
	call symput('repsmonth',repsmonth);
	call symput('monyear',monyear);
	format start end fend date9.;
	run;

%put &Start_dat;
%put &End_dat;
%put &Fend_dat;
%put &monthly.;
%put &month_text.;
%put &month_num.;
%put &monthtext.;
%put &month_name.;
%put &year_text.;
%put &repsmonth.;
%put &short_year;
%put &monyear;

/*RUN THE 30 day REPORT THROUGH*/

%previous_month30_grab_and_drill;	

%end;
%Mend Run_30day_report;



/*Calls to the report_date_control macros here  
These are referenced via the RUN_DECIDER_FAI MACRO below*/

%if &manual_run = Y %then
%do;
	/** want to print to log window when testing **/
	%if &afmsc_testing ne Y %then
		%do;
			proc printto log = "T:\first_time_install_a_&SYSDATE._v1.log"; run;
		%end;
%end;



%if &run_main_overide = Y %then
%do;
%run_Main_report(Run_Main_Report=&YsNMain.,Run_main_for_Prevmon=&YsNMPrev.,Run_early_for_Prevmon=&YsNEPrev., my_today=&my_today.);
%end;

%if &run_early_overide = Y %then
%do;
	%if &afmsc_testing ne Y %then
		%do;
			%run_Early_report(Run_Early_Report=&YsNEarly.,Run_main_for_Prevmon=&YsNMPrev.,Run_early_for_Prevmon=&YsNEPrev., my_today=&my_today.);
		%end;
%end;

%if &run_30_overide = Y %then
%do;
	%if &afmsc_testing ne Y %then
		%do;
			%Run_30day_report(Run_30day_Report=&YsN30day., my_today=&my_today.);
		%end;
%end;
/**/

%mend RUN_DECIDER_FAI;

/*normal day my_today is today and all run flags are set to Y except manual run for log = N*/
%RUN_DECIDER_FAI (	my_today_overide='08JUN2019'd,
					run_early_overide=N,
					run_main_overide=Y,
					run_30_overide=N,
					manual_run=Y	
					);

Re: SAS File in Use

Patrick — Thu, 13 Jun 2019 02:43:41 GMT

@saikiran_nemani

You are using below code:

proc printto log = "T:\first_time_install_a_&SYSDATE._v1.log"; run;

This will create a name which only changes once a day.

If I use this code in two different sessions run at the same time then I can replicate your error. May be add something dynamic to your log name which makes it session specific - i.e. a PID

Re: SAS File in Use

Kurt_Bremser — Thu, 13 Jun 2019 05:55:32 GMT

On top of what @Patrick already said, I would not use &sysdate9 as is in filenames, but instead reformat it to yymmddn8., to get a nice order of files.

I would use

%sysfunc(inputn(&sysdate9.,date9.),yymmddn8.)

in place of &sysdate9.

Re: SAS File in Use

ErikLund_Jensen — Thu, 13 Jun 2019 08:19:36 GMT

Hi @saikiran_nemani

You could also use a small macro to make sure you have a new log file in each run:

%macro setlogfile;
	%let i = 1;
	%let logfile = c:\temp\mylog_%sysfunc(inputn(&sysdate9.,date9.),yymmddd10.)_&i..log;
	%do %while(%sysfunc(fileexist(&logfile)));
		%let i = %eval(&i+1);
		%let logfile = c:\temp\mylog_%sysfunc(inputn(&sysdate9.,date9.),yymmddd10.)_&i..log;
	%end;
	%put INFO: &=logfile;
	proc printto log = "&logfile"; 
	run;
%mend;
%setlogfile;

* program code;
%put line written to log;

* reset log;
proc printto;
run;

This macro gives a log file name with a number suffix added after the date, starting with 1: something_date_1.. If this file exists, the name is generated as something_date_2. and so on:

226
227
228  %macro setlogfile;
229      %let i = 1;
230      %let logfile = c:\temp\mylog_%sysfunc(inputn(&sysdate9.,date9.),yymmddd10.)_&i..log;
231      %do %while(%sysfunc(fileexist(&logfile)));
232          %let i = %eval(&i+1);
233          %let logfile = c:\temp\mylog_%sysfunc(inputn(&sysdate9.,date9.),yymmddd10.)_&i..log;
234      %end;
235      %put INFO: &=logfile;
236      proc printto log = "&logfile";
237      run;
238  %mend;
239  %setlogfile;
INFO: LOGFILE=c:\temp\mylog_2019-06-13_1.log

NOTE: PROCEDURE PRINTTO used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


247
248
249  %macro setlogfile;
250      %let i = 1;
251      %let logfile = c:\temp\mylog_%sysfunc(inputn(&sysdate9.,date9.),yymmddd10.)_&i..log;
252      %do %while(%sysfunc(fileexist(&logfile)));
253          %let i = %eval(&i+1);
254          %let logfile = c:\temp\mylog_%sysfunc(inputn(&sysdate9.,date9.),yymmddd10.)_&i..log;
255      %end;
256      %put INFO: &=logfile;
257      proc printto log = "&logfile";
258      run;
259  %mend;
260  %setlogfile;
INFO: LOGFILE=c:\temp\mylog_2019-06-13_2.log

NOTE: PROCEDURE PRINTTO used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


268  %macro setlogfile;
269      %let i = 1;
270      %let logfile = c:\temp\mylog_%sysfunc(inputn(&sysdate9.,date9.),yymmddd10.)_&i..log;
271      %do %while(%sysfunc(fileexist(&logfile)));
272          %let i = %eval(&i+1);
273          %let logfile = c:\temp\mylog_%sysfunc(inputn(&sysdate9.,date9.),yymmddd10.)_&i..log;
274      %end;
275      %put INFO: &=logfile;
276      proc printto log = "&logfile";
277      run;
278  %mend;
279  %setlogfile;
INFO: LOGFILE=c:\temp\mylog_2019-06-13_3.log

NOTE: PROCEDURE PRINTTO used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds