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
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.
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
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.
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!
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.