BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jl1005
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

@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 ?

View solution in original post

6 REPLIES 6
Reeza
Super User

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
Obsidian | Level 7

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
Super User

@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
Obsidian | Level 7

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
Super User

@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
Opal | Level 21

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 907 views
  • 0 likes
  • 3 in conversation