Proc Transpose

[Timbim]

Hello,

Below is my Proc Transpose logic however some of the records in my output is showing  blank. How can I remove the blank and have the output show on the same row?

Proc transpose data=Acct_Prod

out=Accts3 ( drop=_NAME_ _LABEL_);

by accno bsb Branch Openmth L3;

id month;

var accbal;

run;

Output as per below:

AccNo	bsb	Branch	OpenMth	L3	June	July	August
123456	123	DW	31-Aug-18	S	5000.23
123456	123	DW	31-Aug-18	S	.	5000.23	5000.23

Appreciate your help.

[VDD]

have you tried to set prefix=month

[novinosrin]

Can you illustrate this?

 "How can I remove the blank and have the output show on the same row?"

[Timbim]

I would want my result to be like the following. That's what I mean by removing the blank.

AccNo	bsb	Branch	OpenMth	L3	June	July	August
123456	123	DW	31-Aug-18	S	5000.23	5000.23	5000.23

Thanks kindly

[novinosrin]

Perhaps a data model crux. A temporary solution could be another pass of the transposed dataset and update:

data have;
input (AccNo	bsb	Branch	OpenMth	L3	June	July	August) ($) ;
cards;
123456	123	DW	31-Aug-18	S	5000.23	 	 . .
123456	123	DW	31-Aug-18	S	.	5000.23	5000.23
;

data want;
update have(obs=0) have;
by accno bsb branch ;
run;

[Reeza · Accepted solution]

This actually implies that your BY variables are not exactly the same for some reason. Or at least SAS doesn't think so for some reason. Not sure why, you may need to remove any leading or trailings. And if there's a different in case, ie Dw is not equal to DW then that's one thing that definitely needs to be fixed.

[Timbim]

Thanks Reeza,

I've test the by variables and one of them was different for some reason.

Thanks for that.

[data_null__]

This can also happen when the ID variable is also in the BY statement and can be useful if you want to create a diagonal matrix.

proc transpose data=sashelp.class out=agematrix;
   by name;
   id name;
   var age;
   run;