Macro Loop Over fail

tobyfarms — Fri, 10 Nov 2017 15:11:40 GMT

Hello,

I'm trying to work through a macro that continually fails. This is a macro that should continue to loop and provide multiple output in the results. The error states that I am missing the variable 'Opco' but I have it listed in all my of data step codes.

Here is the Error Log (Most of it, it is long error message).

libname weather2 meta library="weather2" metaout=data;
options symbolgen threads cpucount=actual cleanup;

   
%macro get_filenames(location);
filename _dir_ "%bquote(&location.)";
DATA filenames(keep=memname);
  handle=dopen( '_dir_' );
  if handle > 0 then DO;
    count=dnum(handle);
    DO i=1 to count;
      memname=dread(handle,i);
      output filenames;
    end;
  end;
  rc=dclose(handle);
run;
filename _dir_ clear;
%mend;


%get_filenames(/sas/data/imports);
 


data filenames (keep=memname opco);
   set filenames;
   if (substr(memname,1,6)="wbank_");
   opco = substr(memname,7,3);    *** created variable from positions 7-9: ace dpl pep*;
run;

proc sort data=filenames;
   by opco descending memname;
run;



data filenames;
   set filenames;
   by opco descending memname;
   if first.opco;
run;




 %macro LoopOverDatasets();
 %local datasetCount iter fullname;

 /***********************************************************************/;
 /*  Get number of datasets by counting the records in work.filenames   */;
 /***********************************************************************/;



proc sql noprint;
  select count(*)
  into :datasetCount
  from WORK.filenames;
quit;
  



/*initiate do-loop */;

%let iter=1;   ************ set to 3 to limit procedures;

%do	%while (&iter.<= &datasetCount. );

Proc sort data=filenames (keep=memname opco);  										
   by opco descending memname;  

    /* create variable fullname to retrieve weather data  */;

data work.dataset_list;
   set filenames (firstobs=&iter. obs=&iter.); *only read 1 record;
   by opco descending memname;
   Length passname $41.;
   locname = "'/sas/data/imports/";
   passname = catt(locname,memname);
   fullname = catt(passname,"'");
   output;
run;



Proc sql;
   select fullname
   into :fullname
   from work.dataset_list;
 
filename in_dat &fullname. lrecl=160;

proc import datafile= in_dat 
  dbms=dlm
  out=weath_updt
  replace;
  delimiter='09'x;
  getnames=no;
run; 

data weath_updt(drop=var3 var14 var15 pt yr mo da);
  set weath_updt;
  if var1 = 'CODE' THEN DELETE;
  rename var1 = CODE
         var2 = STATUS
		 var4 = TMP
		 var5 = DPT
		 var6 = HUM 
		 var7 = HID 
		 var8 = WCL 
		 var9 = WDR
		 var10 = WSP 
		 var11 = WET 
		 var12 = CC 
		 var13 = SSM;
  pt=index(var3," ");
  yr = substr(var3,1,4);
  mo = substr(var3,6,2);
  da = substr(var3,9,2);
  /*  HR = sum(substr(var3,pt+1),1);  */
  HOUR = sum(substr(var3,pt+1),1);
  DATE = mdy(mo,da,yr);
  format DATE date7.;
run;

data updt_data 
     updt_forecast;
   set weath_updt;  

   

 tmp_new=input(TMP,best8.); 										
 dpt_new=input(DPT,best8.); 
 hum_new=input(HUM,best8.);
 hid_new=input(HID,best8.); 
 wcl_new=input(WCL,best8.);
 wdr_new=input(WCL,best8.);
 wsp_new=input(WCL,best8.);
 wet_new=input(WCL,best8.);
 cc_new=input(WCL,best8.);
 ssm_new=input(WCL,best8.);


   if status = "O" then output updt_data;
     else output updt_forecast;	 

drop TMP;	 	
drop DPT;			
drop HUM; 			
drop HID; 	
drop WCL;			
drop WDR;			
drop WSP;			
drop WET;	
drop CC ;		
drop SSM;	

rename tmp_new=TMP;
rename dpt_new=DPT;
rename hum_new=HUM;
rename hid_new=HID;
rename wcl_new=WCL;
rename wdr_new=WDR;
rename wsp_new=WSP;
rename wet_new=WET;
rename cc_new=CC;	
rename ssm_new=SSM;

run;



data Weather_data_2;
   /*Set Weather2.Weather_data2; */					
Set Weather2.Weather_ACTUAL;


proc sort data=Weather_data_2;
   by DATE HOUR/*HR*/;
run;


proc sort data=updt_data;
   by DATE HOUR/*HR*/;
run;


data weather2.Weather_data_2b;
   merge Weather_data_2(in=w) updt_data(in=u);
   by DATE HOUR/*HR*/;
   if (w) or (u and not w);
run;


data Weather_Frcast;
   Set Weather2.Weather_Forecast;



 tmp_new=input(TMP,best8.); 
 dpt_new=input(DPT,best8.); 
 hum_new=input(HUM,best8.);
 hid_new=input(HID,best8.); 
 wcl_new=input(WCL,best8.);
 wdr_new=input(WCL,best8.);
 wsp_new=input(WCL,best8.);
 wet_new=input(WCL,best8.);
 cc_new=input(WCL,best8.);
 ssm_new=input(WCL,best8.);

drop TMP;	 	
drop DPT;			
drop HUM; 			
drop HID; 	
drop WCL;			
drop WDR;			
drop WSP;			
drop WET;	
drop CC ;		
drop SSM;	

rename tmp_new=TMP;
rename dpt_new=DPT;
rename hum_new=HUM;
rename hid_new=HID;
rename wcl_new=WCL;
rename wdr_new=WDR;
rename wsp_new=WSP;
rename wet_new=WET;
rename cc_new=CC;	
rename ssm_new=SSM;

/***********************************************************************************/


proc sort data=Weather_Frcast;
   by DATE /*HOUR*/ HR;
run;

proc sort data=updt_forecast;
   by DATE HOUR;
  run;


  		
data updt_forecast;
set updt_forecast;
HR=Hour;
drop hour;
run;
	


data Weather_Frcast;
   merge Weather_Frcast(in=w) updt_forecast(in=u);
   by DATE HR;
   if (w) or (u and not w);
run;

proc sort data=Weather_Frcast;
   by DATE /*HOUR*/ HR;


data Weather2.WForecast;
   set Weather_Frcast;
run;

	

    /*increment the iterator of the loop*/;

%let iter=%eval(&iter.+1);

%end;  *end of do-loop;
%MEND LoopOverDatasets; 




/* Recreate file list to prove the update files have been removed. ;   */
 
%macro get_filenames(location);
filename _dir_ "%bquote(&location.)";
data filenames(keep=memname opco); 																 
  handle=dopen( '_dir_' );
  if handle > 0 then do;
    count=dnum(handle);
    do i=1 to count;
      memname=dread(handle,i);
      output filenames;
    end;
  end;
  rc=dclose(handle);
run;
filename _dir_ clear;
%mend;

%get_filenames(/sas/data/imports);

 

 
data filenames;
   set filenames;
   by opco descending memname;
  if first.opco; 

data filenames;
   set filenames;
   proc print;
   title "list most recent" ;
run;


/*
%mend;

/*call the macro for next pass through list of filenames*/



%LoopOverDatasets()
run;

NOTE: There were 307 observations read from the data set WORK.WEATHER_FRCAST.

NOTE: The data set WEATHER2.WFORECAST has 307 observations and 14 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

SYMBOLGEN: Macro variable ITER resolves to 149

SYMBOLGEN: Macro variable ITER resolves to 150

SYMBOLGEN: Macro variable DATASETCOUNT resolves to 157

ERROR: The variable opco in the DROP, KEEP, or RENAME list has never been referenced.

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

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

SYMBOLGEN: Macro variable ITER resolves to 150

ERROR: BY variable opco is not on input data set WORK.FILENAMES.

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

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

WARNING: Data set WORK.DATASET_LIST was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.02 seconds

NOTE: No rows were selected.

SYMBOLGEN: Macro variable FULLNAME resolves to

ERROR: Invalid physical name.

ERROR: Error in the FILENAME statement.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to

WORK.PARMS.PARMS.SLIST.

ERROR: Physical file does not exist, /sas/config/Lev1/SASApp/IN_DAT.

ERROR: Import unsuccessful. See SAS Log for details.

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

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.02 seconds

cpu time 0.02 seconds

ERROR: File WORK.WEATH_UPDT.DATA does not exist.

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).

353:153 353:178 353:203 353:228 353:9

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).

479 The SAS System 08:40 Friday, November 10, 2017

353:2 353:37 353:40 353:43

WARNING: The variable var14 in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable var15 in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable var2 in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable var4 in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable var5 in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable var6 in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable var7 in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable var8 in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable var9 in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable var10 in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable var11 in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable var12 in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable var13 in the DROP, KEEP, or RENAME list has never been referenced.

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

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

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).

35189:154 35189:193 35189:222 35189:250 353:27 353:55 353:83 353:111 353:138 353:166

NOTE: Variable TMP is uninitialized.

NOTE: Variable DPT is uninitialized.

NOTE: Variable HUM is uninitialized.

NOTE: Variable HID is uninitialized.

NOTE: Variable WCL is uninitialized.

NOTE: Variable status is uninitialized.

WARNING: The variable WDR in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable WSP in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable WET in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable CC in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable SSM in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable WDR in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable WSP in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable WET in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable CC in the DROP, KEEP, or RENAME list has never been referenced.

WARNING: The variable SSM in the DROP, KEEP, or RENAME list has never been referenced.

NOTE: There were 0 observations read from the data set WORK.WEATH_UPDT.

NOTE: The data set WORK.UPDT_DATA has 0 observations and 14 variables.

NOTE: The data set WORK.UPDT_FORECAST has 0 observations and 14 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

NOTE: There were 948656 observations read from the data set WEATHER2.WEATHER_ACTUAL.

NOTE: The data set WORK.WEATHER_DATA_2 has 948656 observations and 14 variables.

NOTE: DATA statement used (Total process time):

real time 0.38 seconds

cpu time 0.38 seconds

NOTE: There were 948656 observations read from the data set WORK.WEATHER_DATA_2.

NOTE: The data set WORK.WEATHER_DATA_2 has 948656 observations and 14 variables.

480 The SAS System 08:40 Friday, November 10, 2017

NOTE: PROCEDURE SORT used (Total process time):

real time 0.33 seconds

cpu time 0.73 seconds

NOTE: Input data set is empty.

NOTE: The data set WORK.UPDT_DATA has 0 observations and 14 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: There were 948656 observations read from the data set WORK.WEATHER_DATA_2.

NOTE: There were 0 observations read from the data set WORK.UPDT_DATA.

NOTE: The data set WEATHER2.WEATHER_DATA_2B has 948656 observations and 14 variables.

NOTE: DATA statement used (Total process time):

real time 0.41 seconds

cpu time 0.41 seconds

NOTE: There were 307 observations read from the data set WEATHER2.WEATHER_FORECAST.

NOTE: The data set WORK.WEATHER_FRCAST has 307 observations and 14 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

NOTE: There were 307 observations read from the data set WORK.WEATHER_FRCAST.

NOTE: The data set WORK.WEATHER_FRCAST has 307 observations and 14 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: Input data set is empty.

NOTE: The data set WORK.UPDT_FORECAST has 0 observations and 14 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: There were 0 observations read from the data set WORK.UPDT_FORECAST.

NOTE: The data set WORK.UPDT_FORECAST has 0 observations and 14 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: There were 307 observations read from the data set WORK.WEATHER_FRCAST.

NOTE: There were 0 observations read from the data set WORK.UPDT_FORECAST.

NOTE: The data set WORK.WEATHER_FRCAST has 307 observations and 14 variables.

NOTE: DATA statement used (Total process time):

481 The SAS System 08:40 Friday, November 10, 2017

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: There were 307 observations read from the data set WORK.WEATHER_FRCAST.

NOTE: The data set WORK.WEATHER_FRCAST has 307 observations and 14 variables.

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: There were 307 observations read from the data set WORK.WEATHER_FRCAST.

NOTE: The data set WEATHER2.WFORECAST has 307 observations and 14 variables.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

SYMBOLGEN: Macro variable ITER resolves to 150

SYMBOLGEN: Macro variable ITER resolves to 151

SYMBOLGEN: Macro variable DATASETCOUNT resolves to 157

ERROR: The variable opco in the DROP, KEEP, or RENAME list has never been referenced.

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

NOTE: PROCEDURE SORT used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

SYMBOLGEN: Macro variable ITER resolves to 151

ERROR: BY variable opco is not on input data set WORK.FILENAMES.

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

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

WARNING: Data set WORK.DATASET_LIST was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

NOTE: No rows were selected.

SYMBOLGEN: Macro variable FULLNAME resolves to

ERROR: Invalid physical name.

ERROR: Error in the FILENAME statement.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

cpu time 0.01 seconds

NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to

WORK.PARMS.PARMS.SLIST.

ERROR: Physical file does not exist, /sas/config/Lev1/SASApp/IN_DAT.

ERROR: Import unsuccessful. See SAS Log for details.

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

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.02 seconds

482 The SAS System 08:40 Friday, November 10, 2017