topic Proc Transpose Missing Field in New SAS User

Proc Transpose Missing Field

Will_FBB — Mon, 19 Aug 2019 18:51:39 GMT

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.

Re: Proc Transpose Missing Field

PeterClemmensen — Mon, 19 Aug 2019 18:55:40 GMT

Welcome to the SAS Community 🙂

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

Re: Proc Transpose Missing Field

Will_FBB — Mon, 19 Aug 2019 19:02:17 GMT

Re: Proc Transpose Missing Field

ballardw — Mon, 19 Aug 2019 19:28:59 GMT

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.

Re: Proc Transpose Missing Field

PGStats — Mon, 19 Aug 2019 19:33:29 GMT

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

Re: Proc Transpose Missing Field

Will_FBB — Mon, 19 Aug 2019 19:38:17 GMT

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.

Re: Proc Transpose Missing Field

PeterClemmensen — Mon, 19 Aug 2019 19:42:25 GMT

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;

Re: Proc Transpose Missing Field

ballardw — Mon, 19 Aug 2019 21:23:38 GMT

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

Re: Proc Transpose Missing Field

Will_FBB — Mon, 19 Aug 2019 21:38:12 GMT

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.

Re: Proc Transpose Missing Field

Reeza — Mon, 19 Aug 2019 21:41:22 GMT

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.

Re: Proc Transpose Missing Field

Will_FBB — Mon, 19 Aug 2019 21:49:57 GMT

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

Re: Proc Transpose Missing Field

Reeza — Mon, 19 Aug 2019 21:59:19 GMT

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

Re: Proc Transpose Missing Field

ballardw — Mon, 19 Aug 2019 22:34:17 GMT

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

Re: Proc Transpose Missing Field

Will_FBB — Tue, 20 Aug 2019 13:50:36 GMT

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!

Re: Proc Transpose Missing Field

Reeza — Tue, 20 Aug 2019 15:00:39 GMT

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.

Re: Proc Transpose Missing Field

Tom — Tue, 20 Aug 2019 17:40:39 GMT

A common trick it to introduce a new new group in the data that has all of the values. This is also frequently done to insure that the names are created in the order wanted. You can even do this using a data step view so that you don't need to make a second copy of your original data. You can then remove the extra group from the output dataset. You should also use the PREFIX= option to tell PROC TRANSPOSE how to name the new variables since names like 1,2,3 etc are not really valid or useful.

data test_fixed / view=test_fixed;
  if 0 then set test;
  if _n_=1 then do month=1 to 49; output; end;
  set test;
run;

proc transpose data=test_fixed prefix=MONTH
  out=test_t(where=(not missing(id)))
;
  by ID;
  id month;
  var sale;
run;