Force in Needed Observation in Proc Freq

[jl1005]

Hi,

I'm working on creating transition matrices, but now am running into issues. Previoulsy, I had posted this topic to output the datasets. But now that I'm narrowing down the input datasets, there are times where not all possible combinations occur, which then throws off using them to simulate down the road. Here's the code that was being used to originally create them: 

proc freq data = &team._a_class_&i;	
	tables new_start_bases_cd*new_end_bases_cd / noprint sparse out=need1a;
	tables new_start_bases_cd / noprint out=need1b;
	run;
data need2;
	  merge need1a  need1b (rename=(count=row_total));
	  by new_start_bases_cd;
	  percent=count/row_total;
	  drop count row_total;
	run;
proc transpose data=need2 out=&team._a_class_&i._data (drop=_name_ _label_) prefix=percent_;
	  by new_start_bases_cd;
	  var percent;
	  id new_end_bases_cd;	 
	run;

The macro variables are in there in order to repeat this 15 times and for different teams, in order to simplify the process. Ideally, the output would be a 24 row, by 32 column data table. But since the datasets have been narrowed down, there are instances where its 20x26 or 23x27.

So is there a way to force the number of rows and column in the output, even if they would be filled with all 0's?

Thank you.

[Reeza]

If you need all the levels you need to have them defined somewhere at least once. 

This can be a dataset or manually but it has to exist somewhere. The ways to account for this are to use PRELOADFMT or to merge it with a master dataset that has all the combinations defined already. 

[jl1005]

I tried the preloadfmt approach, but still wasn't getting the missing variable combinations. Do you think it would be easier to create a master dataset with all the possible combinations and merge that with the dataset that's then used to create the matrix?

Here's the code I had tried: 

proc tabulate data= min_h_class_15 out=min_h_class_15_tab;
	class new_start_bases_cd new_end_bases_cd / preloadfmt;
	TABLE new_start_bases_cd * ROWPCTN , new_end_bases_cd / printmiss;
	run;
proc transpose data=min_h_class_15_tab out=min_h_class_15_data_trans (drop=_name_) prefix=percent_;;
	by new_start_bases_cd;
	var pctn_10;
	id new_end_bases_cd;
	run;

The transpose is just to get it into a data table. 

[Reeza]

---

@jl1005 wrote:

I tried the preloadfmt approach, but still wasn't getting the missing variable combinations. Do you think it would be easier to create a master dataset with all the possible combinations and merge that with the dataset that's then used to create the matrix?

Here's the code I had tried: 

proc tabulate data= min_h_class_15 out=min_h_class_15_tab;
	class new_start_bases_cd new_end_bases_cd / preloadfmt;
	TABLE new_start_bases_cd * ROWPCTN , new_end_bases_cd / printmiss;
	run;
proc transpose data=min_h_class_15_tab out=min_h_class_15_data_trans (drop=_name_) prefix=percent_;;
	by new_start_bases_cd;
	var pctn_10;
	id new_end_bases_cd;
	run;

The transpose is just to get it into a data table. 

---

Where's the format?

[jl1005]

Sorry, I don't think I fully understood preloadfmt when I used it. So I would need to do a proc format above, and define each class?

[Reeza · Accepted solution]

---

@jl1005 wrote:

Sorry, I don't think I fully understood preloadfmt when I used it. So I would need to do a proc format above, and define each class?

---

Yes, you need some way to tell SAS what values are possible, especially if they're not in your data. How else would it even know those values exist ?

[art297]

There are a number of methods available for both proc frequency and proc means. Take a look at: https://www.pharmasug.org/proceedings/2012/CC/PharmaSUG-2012-CC26.pdf

Art, CEO, AnalystFinder.com