BookmarkSubscribeRSS Feed
MikeFranz
Quartz | Level 8

Hi,

 

I've seen a couple of similar threads, but haven't been able to use them to answer my question. I have the following dataset:

 

Address    Year   Account   Amount
Address1   2015   Account1  100
Address1   2015   Account2  200
Address1   2016   Account1  50
Address2   2015   Account1  150
Address2   2015   Account2  10
Address2   2016   Account1  50
Address2   2016   Account1  30

I would like to transpose this, such that each year-account combination is a variable, as below:

 

Address    Year   Account   Amount _2015_Account1 _2015_Account2 _2016_Account1 _2016_Account2
Address1   2015   Account1  100    100            .              .              .     
Address1   2015   Account2  200    .              200            .              .
Address1   2016   Account1  50     .              .              50             .
Address2   2015   Account1  150    150            .              .              . 
Address2   2015   Account2  10     .              10             .              .
Address2   2016   Account1  50     .              .              50             .
Address2   2016   Account2  30     .              .              .              30

I would then like to aggregate such that each address is only shown once, as below:

Address    _2015_Account1 _2015_Account2 _2016_Account1 _2016_Account2
Address1   100            200            50             .     
Address2   150            10             50             30 

 

Any thoughts on how I could do the above by dynamically creating the columns? I'm currently creating each column manually, but there are 15 accounts, and 5 years 🙂

 

 

 

4 REPLIES 4
Reeza
Super User

PROC TRANSPOSE.

 

proc transpose data=have out=want;
by address;
var amount;
id year account;
run;

If this doesn't work, post your code, log and an explanation of exactly how it's not meeting your requirements.

 


@MikeFranz wrote:

Hi,

 

I've seen a couple of similar threads, but haven't been able to use them to answer my question. I have the following dataset:

 

Address    Year   Account   Amount
Address1   2015   Account1  100
Address1   2015   Account2  200
Address1   2016   Account1  50
Address2   2015   Account1  150
Address2   2015   Account2  10
Address2   2016   Account1  50
Address2   2016   Account1  30

I would like to transpose this, such that each year-account combination is a variable, as below:

 

Address    Year   Account   Amount _2015_Account1 _2015_Account2 _2016_Account1 _2016_Account2
Address1   2015   Account1  100    100            .              .              .     
Address1   2015   Account2  200    .              200            .              .
Address1   2016   Account1  50     .              .              50             .
Address2   2015   Account1  150    150            .              .              . 
Address2   2015   Account2  10     .              10             .              .
Address2   2016   Account1  50     .              .              50             .
Address2   2016   Account2  30     .              .              .              30

I would then like to aggregate such that each address is only shown once, as below:

Address    _2015_Account1 _2015_Account2 _2016_Account1 _2016_Account2
Address1   100            200            50             .     
Address2   150            10             50             30 

 

Any thoughts on how I could do the above by dynamically creating the columns? I'm currently creating each column manually, but there are 15 accounts, and 5 years 🙂

 

 

 


 

MikeFranz
Quartz | Level 8
That worked, thank you! Significantly more elegant than my 200 line code, very useful PROC 🙂
PaigeMiller
Diamond | Level 26

In your desired output, before aggregation, you have Address2 2016 Account2

 

but there is no such combination in your input data. How can this be?

 

Why do you need columns? Why isn't the original layout where there is one record on a row sufficient? What are you going to do next that these columns are needed? They are not needed for aggregating by Address.

--
Paige Miller
ballardw
Super User

And if you are looking for a report that people read without manipulating data at all.

 

proc tabulate data=have;

class address year account;

var amount;

table address,

         year='' *account=''*amount*sum=''*f=best10.;

run;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1438 views
  • 3 likes
  • 4 in conversation