topic Re: Proc Transpose in SAS Programming

Proc Transpose

Timbim — Thu, 25 Oct 2018 03:10:50 GMT

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.

Re: Proc Transpose

VDD — Thu, 25 Oct 2018 03:28:50 GMT

have you tried to set prefix=month

Re: Proc Transpose

novinosrin — Thu, 25 Oct 2018 03:37:49 GMT

Can you illustrate this?

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

Re: Proc Transpose

Reeza — Thu, 25 Oct 2018 03:50:21 GMT

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.

Re: Proc Transpose

Timbim — Thu, 25 Oct 2018 04:33:31 GMT

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

Re: Proc Transpose

novinosrin — Thu, 25 Oct 2018 04:43:25 GMT

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;

Re: Proc Transpose

Timbim — Thu, 25 Oct 2018 05:17:05 GMT

Thanks Reeza,

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

Thanks for that.

Re: Proc Transpose

data_null__ — Thu, 25 Oct 2018 11:47:35 GMT

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;