topic Re: SAS: Rotating a dataset in SAS Programming

SAS: Rotating a dataset

MikeFranz — Tue, 04 Jul 2017 14:50:50 GMT

Hi,

I have seen a couple of posts on this, but I still can't get my code to work.

I have a dataset which has the following layout:

Address Account     Period      Amount
Prop 1  Rent        31/01/2017  1500
Prop1   Rent        28/02/2017  1500
Prop1   Insurance   31/01/2017  -400
Prop1   Insurance   31/02/2017  -50
Prop1   Insurance   28/01/2017  -350
Prop1   PM Fees     31/01/2017  -100
Prop2   Rent        31/01/2017  600
Prop2   Rent        31/01/2017  600
Prop2   Rent        28/02/2017  1200

etc.

I am looking to rotate this dataset such that the output is as below:

Address Account    31/01/2017 28/02/2017
Prop1    Rent      1500        1500
Prop1   Insurance  -400        -350
Prop1   PM Fees    -100           0
Prop2   Rent       1200        1200

etc. i.e. I would like to aggregate up across Address and Account, and then rotate the Period column into variables, such that there is only one row for each Address/Account pair.

Could anyone help me with this?

Thanks,

Mike

Re: SAS: Rotating a dataset

RW9 — Tue, 04 Jul 2017 15:06:11 GMT

The task is called transposing going from logn to wide.

For your specific taks you can do it in a number of ways, proc summary for instance, arrays etc. I will show doing the two steps separately:

proc sql;
  create table INTER as
  select  ADDRESS, 
          ACCOUNT,
          PERIOD,
          sum(AMOUNT) as AMOUNT
  from    HAVE
  group by ADDRESS,ACCOUNT,PERIOD;
quit;

proc transpose data=inter out=want;
  by address account;
  var amount;
  idlabel period;
run;

Note that the above will only label the variables with the date, its really not a good idea to name the variables per the date as it makes your code 10x more difficult then.

Re: SAS: Rotating a dataset

art297 — Tue, 04 Jul 2017 15:15:10 GMT

Similar to @RW9's response, but using proc summary and proc transpose. Regardless, with using either method, I think you want to use id rather than idlabel.

Also, your data had some errors in it like a date of Feb 31 and an extra space in one of the addresses:

data have;
  informat Account $10.;
  informat Period anydtdte10.;
  format Period ddmmyy10.;
  input Address $ Account &    Period      Amount;
  cards;
Prop1  Rent        31/01/2017  1500
Prop1   Rent        28/02/2017  1500
Prop1   Insurance   31/01/2017  -400
Prop1   Insurance   31/01/2017  -50
Prop1   Insurance   28/01/2017  -350
Prop1   PM Fees     31/01/2017  -100
Prop2   Rent        31/01/2017  600
Prop2   Rent        31/01/2017  600
Prop2   Rent        28/02/2017  1200
;

proc summary data=have nway;
  class Address Account Period;
  var amount;
  output out=need (drop=_:) sum=;
run;

proc transpose data=need out=want;
  by Address Account;
  var Amount;
  id Period;
run;

Art, CEO, AnalystFinder.com

Re: SAS: Rotating a dataset

RW9 — Tue, 04 Jul 2017 15:28:52 GMT

Hi @art297, I was particularly avoiding the use of id as having columns named:

_31_01_201 etc.

Are really difficult to program with. Fine with putting the date in the label - although it becomes useless then - but definately not as variable names.

Re: SAS: Rotating a dataset

MikeFranz — Tue, 04 Jul 2017 16:32:25 GMT

Hi guys,

Thanks for the responses, it is now kind of working, as per below:

proc sql;
	create table agg_transactions as
		select Address, 'Mapped Account'n, Period, Period_Name, Sum(Amount) as amount
		from tf.transactions_&fund
		group by Address, 'Mapped Account'n, Period, Period_Name;
quit;
proc transpose data=agg_transactions out = work.cashflows (drop = _NAME_);
	by Address 'Mapped Account'n;
 	var Amount; 
	id Period_Name;
run;

What I've done is create a new variable "Period_Name" as a character with format "Date." (e.g. 31Jan17) and I've then tried to use this as my grouping variable and my ID, which seems to work, and I think gets around the problem of using dates as variable names?

Many thanks for your help!

Re: SAS: Rotating a dataset

RW9 — Tue, 04 Jul 2017 17:54:49 GMT

Not really. The reason is this:

Labels - these are free text used for display purposes and have no other function so can contain anything.

Names - these are used only in the programming. For logical operations these should be simple and consistent to ease programming activity.

Take an example, you need to alter each of the variables in your transposed dataset, now if you know where the first is and the last you can simplfy to:

data want;
  set have;
  array vals{*} _31_02_2017--_12_01_2019;
  do i=1 to dim(vals);
    ...;
  end;
  final_total=sum(_31_02_2017--_12_01_2019);
run;

However if you have a consistent naming convention (e.g. col1 col2 etc.) you can simplify quite a lot:

data want;
  set have;
  array vals{*} col:;
  do i=1 to dim(vals);
    ...;
  end;
  final_total=sum(of col:);
run;

Just in this simple example you can see how consistent naming simplfies the code and simple code leads to easy to read and maintain code.

Second to this, your putting "data" into a place where it is hard to process, say you want to manipulate date, move it one day forward, how would you go about doing that, maybe a mass of macro code looping over metadata? Whereas keep the date in the data, makes processing simpler.