Proc Transpose Missing Field

[Will_FBB]

I have  49 month on the data set ( 1 - 49 vertically), when I transpose the data set, I can see month 1 - 47 horizontally as expected but missing month 48 and 49. Does anybody have any idea what might cause this? I already tried to fill blank values with dummy numbers.

proc transpose data=test out=test_t;
id month;
by ID;
var sale;
run;

Thanks in advance.

[PeterClemmensen]

Welcome to the SAS Community 🙂

Can you post an example of how your data is structured? Makes it much easier to help you

[PGStats]

What does the SAS log say in response to the proc transpose call?

[ballardw]

How exactly did you determine that month 48 and month 49 are missing? Proc Contents? Proc Print? Table view?

"See" is not exactly a precise description as we do not have your settings, system or data.

And what exactly did you do to attempt to "fill blank values with dummy numbers"? That might be an issue.

Since your posted PDF apparently has records for a single ID and does not have values for months 31 through 47 it would be very hard to be sure as to the cause for your full data set.

[Will_FBB]

It has thousands of ID in the data set, it misses some months here and there for some ID's, but it does have ID's that have valid values from month 1 - 49. If I have to clean up the data first, it almost defeat the purpose of using transpose.

[PeterClemmensen]

Your code looks right to me.. 

data test;
	do id=1 to 3;
		do month=1 to 49;
			sale=rand('integer', 1, 100);
			output;
		end;
	end;
run;

proc transpose data=test out=test_t;
	id month;
	by ID;
	var sale;
run;

[ballardw]

---

@Will_FBB wrote:

It has thousands of ID in the data set, it misses some months here and there for some ID's, but it does have ID's that have valid values from month 1 - 49. If I have to clean up the data first, it almost defeat the purpose of using transpose.

---

Does not address how you determined missing in the output.

And did you determine that the values were missing before attempting to "clean the data"? If not, then your cleaning attempt could be issue.

Sharing thousands of records is not needed but to help us feel a bit more secure in understanding your actual data can you share the actual output of Proc Freq on the Month variable?

[Will_FBB]

I think there is a misunderstanding here. When I say month 48 and 49 are missing, I mean the entire fields are missing. After transpose, month 1 - 49  would become field names. Field/Column 48 and 49 are just not there in the output data table.

[Reeza]

If the log isn't showing an error or warning this means the data is not in your data set as indicated. Please show the results from a proc means or proc freq on the raw data that would indicate the presence of these values.

[Will_FBB]

this is proc tabulate result, this shows you it has valid value for month 48 - 49

[Reeza]

These types of issues are usually do to a few causes:

1. User error - accidentally referencing the incorrect data set

2. Errors/Warning/Notes in the log that are missed/ignored

3. PRELOADFMT being used in tabulate which cannot be used in transpose

4. Formats applied to a variable in one proc and not another

You need to show your full code and log for us to verify what's happening now. If you cannot for whatever reason share your code/log, or produce a reasonably replicated situation that mimics your issue, you'll need to contact SAS Tech Support and work with them. 

PROC TRANSPOSE is a proc that's used by many users and any issues would affect a lot of production programs. It's possible there's an issue but not very probable. You can always restart SAS and see if the issue persists. 

EDIT: I can't open attachments so I'll have to take your word for it that the data is there. 

---

@Will_FBB wrote:

this is proc tabulate result, this shows you it has valid value for month 48 - 49

---

[ballardw]

---

@Will_FBB wrote:

I think there is a misunderstanding here. When I say month 48 and 49 are missing, I mean the entire fields are missing. After transpose, month 1 - 49  would become field names. Field/Column 48 and 49 are just not there in the output data table.

---

And I have asked twice exactly how you determine that how those fields are missing. From the picture of your data in the first PDF it appears that you may be using EG in some form. I am not sure that the table viewer always shows everything from questions people have posed here. So I ask how you know. And if you ran your "cleaning code", especially if you used a form of:

data thisset;

   set thisset;

you have completely replace the original data set and what you have now has only a slight resemblance to the output of proc transpose.

I did a quickish test and had no problem transposing 65,000 records with 49 values of month with the code you show. I suspected a possibly memory issue as I have seen that from proc transpose though usually with more variables involved. A second consideration is the actual system limits you might have imposed by your Admins. My example data set was about 75Mbytes and the transposed 26Mbytes. If you have an allocation limit of disk space maybe something lost, but I would expect a note, warning or error in the log in that case.

[Will_FBB]

I know if I clean the data, it will come out okay. I just wonder there might be some system setting that I could adjust. Thanks for your input!

[Reeza]

Run a proc freq with the SPARSE option to get all possible combinations and then run your proc transpose on that output. It will include all possible values.