SAS Proc UCM ODS tables output for multiple iterations

[VinodKavery]

I am trying to run multiple iterations(5400) of various combinations of predictors in Proc UCM. The SAS code with all combination of variables has been sequentially generated using an external programming code like csharp etc.

I think this can be done in SAS but thats a topic for next time.

I want to compile all the parameter estimates of all iterations into one table and all the "Smoothed estimate of sum of all components except the irregular component" into another and write some conditions on it.

Currently i am writing all the output to csv for no good.I just know i can use ODS option and specify "ParameterEstimates" and "SmoothedAllExceptIrreg" but to do it for whole 5400 iterations is something i cant figure out.

My sample code for 4 iterations is as below (1347 1348 1349 1350 are the iterations sequentially geneared based on combinations):

/Level with variance,Season without variance,Slope without variance/

ods csv file = "D:\SAS\Iteration_Hair_Care_Volume_1347.csv";
proc ucm data = project.Compiled printall;irregular ;level ;forecast  lead = 8 alpha = 0.05 outfor = project.Compiled_Output_Hair;model 
HC = 
Gr_CPI
gdp;season length=4 
var = 0 noest;slope 
var = 0 noest;
where count > 36;run;quit;ods csv.close;



/*Level with variance,Season without variance,Slope with Variance*/ods csv file = "D:\SAS\Iteration_Hair_Care_Volume_1348.csv";
proc ucm data = project.Compiled printall;irregular ;level ;forecast  lead = 8 alpha = 0.05 outfor = project.Compiled_Output_Hair;model 
HC = 
Gr_CPI
Earning_Avg_Lag;season length=4 
var = 0 noest;slope ;
where count > 36;run;quit;ods csv.close;



/*Level with variance,Season with variance,Slope without Variance*/ods csv file = "D:\SAS\Iteration_Hair_Care_Volume_1349.csv";
proc ucm data = project.Compiled printall;irregular ;level ;forecast  lead = 8 alpha = 0.05 outfor = project.Compiled_Output_Hair;model 
HC = 
Gr_CPI
pdi
season length=4 ;slope 
var = 0 noest;
where count > 36;run;quit;ods csv.close;



/*Level with variance,Season with variance,Slope with Variance*/ods csv file = "D:\SAS\Iteration_Hair_Care_Volume_1350.csv";
proc ucm data = project.Compiled printall;irregular ;level ;forecast  lead = 8 alpha = 0.05 outfor = project.Compiled_Output_Hair;model 
HC = 
Gr_CPI
IIP;season length=4 ;slope ;
where count > 36;run;quit;ods csv.close;

I can append iteration numbers to outfor=xyz dataset but i dont know what good comes out of it. How do i automate this. Kindly help me with this.

[Reeza · Accepted solution]

Since I'm assuming you've built this code automatically using C # or some other language, the simplest modification is as you've suggested, change to capture the ODS table with the index, and append all the datasets together at the end.

ods output parameterEstimates = PE1347;
proc ucm data = project.Compiled print all;
where count > 36;
irregular ;
level ;
forecast  lead = 8 alpha = 0.05 outfor = project.Compiled_Output_Hair;
model  HC = Gr_CPI gap;
season length=4  var = 0 nest;
slope var = 0 noest;
run;quit;

Then you can append all the datasets together and identify the source dataset using the INDSNAME option. This would be after all your models were run, you can reference the datasets in shorthand notation as below. 

data combined;

set PE1-PE5400 INDSNAME = source;

model = source;

run;

This is the simplest method given your current situation. There's a small possibility of running out of space if you're creating thousands of tables. If this is the case you may need to redesign the code to run the model, append the data to a master dataset, erase temporary datasets and repeat. 

Hope this helps, but if anything is unclear feel free to ask. 

[VinodKavery]

I tried it on 3 sample iterations. Please find below my code & queries at the end of it. My objective is to filter out model iterations where all its variables pass t-stat test. 

/*objective: to extract t-stat values for each iteraton*/
ods output parameterEstimates = Project.PE1;
proc ucm data = Project.Compiled printall;
irregular ;
level
var = 0 noest;
forecast lead = 8 alpha = 0.05 outfor = Project.Compiled_Output_Hair;
model
HC =
CPI
GDP_Nominal_LCU;
season length=4 var = 0 noest;
where count > 41;
run;
quit;

ods output parameterEstimates = Project.PE2;
proc ucm data = Project.Compiled printall;
irregular ;
level
var = 0 noest;
forecast lead = 8 alpha = 0.05 outfor = Project.Compiled_Output_Hair;
model
HC =
CPI
PDI_Real;
season length=4 var = 0 noest;
where count > 41;
run;
quit;

ods output parameterEstimates = Project.PE3;
proc ucm data = Project.Compiled printall;
irregular ;
level
var = 0 noest;
forecast lead = 8 alpha = 0.05 outfor = Project.Compiled_Output_Hair;
model
HC =
Gr_CPI
GDP_Nominal_LCU;
season length=4 var = 0 noest;
where count > 41;
run;
quit;

/*objective: if the variables meet criteria, then they pass the t-test*/
data Project.combined (keep = Component tValue Model Result);
set Project.PE1-Project.PE3 INDSNAME = source;
model = source;
if (Component = 'Irregular') then delete;
if (Component = 'CPI' and tvalue le -1.5) then Result = 1;
else if (Component = 'GDP_Nominal_LCU' and tvalue ge 1.5) then Result = 1;
else if (Component = 'PDI_Real' and tvalue ge 1.5) then Result = 1;
else if (Component = 'Gr_CPI' and tvalue le -1.5) then Result = 1;
else Result = 0;
run;
/*objective: to figure out whether all the variables in an iteration (Model=source) has passed*/
Proc Means Data=Project.combined mean;
Class Model;
Var Result;
output out=Project.Final mean=;
Run;
/*objective: if all variables have passed the t-test in an iteration, then filter out those iterations*/
data Project.Final2 (keep = Model Result);
set Project.Final;
where Result = 1;
run;

1. Is there any way i need not print the sas7bdat files for all iterations to library to save space?
2. Is it mandatory to give printall option in proc UCM statement
3. can i avoid the dataset option outfor = ?
4. Is there a better way to code the last 3 steps to filter out since i think it is hardcoding

Thank you for your patience

[VinodKavery]

Thanks a lot reeza. I didn't know about INDSNAME. Let me run the multiple iterations and come back to you. Even I am vary about the space factor.
I do have a query on SmoothedAllExceptIrreg table. I 'll post it with a sample output so it's easy to relate.