Hi Experts!
I'm new to this platform as well as SAS. Please help me out. I was trying to do proc transpose of a dataset with multiple variables and I wish to get all of them in columns and also the year sorting be done in ascending order. Below is the example.
data have;
input Id FirstName LastName year sales $ debt $;
cards;
ab1 Sam Tim 2020 100 0
ab1 Sam Tim 2021 50 70
cd2 Long Phelp 2019 225 10
ef3 Pam Trace 2022 15 15
;
Ran this query -
proc transpose data=have out=want;
by Id FirstName LastName;
id year;
var sales debt;
run;
Got this -
ID | Firstname | Lastname | _name_ | 2020 | 2021 | 2022 | 2019 |
ab1 | Sam | Tim | sales | 100 | 50 | ||
ab1 | Sam | Tim | debt | 70 | |||
cd2 | Long | Phelp | sales | 225 | |||
cd2 | Long | Phelp | debt | 10 | |||
ef3 | Pam | Trace | sales | 15 | |||
ef3 | Pam | Trace | debt | 15 |
Want this -
sales | debt | |||||||||
ID | Firstname | Lastname | 2019 | 2020 | 2021 | 2022 | 2019 | 2020 | 2021 | 2022 |
ab1 | Sam | Tim | 100 | 50 | 70 | |||||
cd2 | Long | Phelp | 225 | 10 | ||||||
ef3 | Pam | Trace | 15 | 15 |
Please let me know how can this be done.
Thank you!
You can't have PROC TRANSPOSE create a SAS data set with the layout that you described where "Sales" is above 2019. There is no such thing as a variable in a data set that has a name that has two parts, one above the other.
You can get this via PROC REPORT.
data have;
input Id $ FirstName $ LastName $ year sales debt;
cards;
ab1 Sam Tim 2020 100 0
ab1 Sam Tim 2021 50 70
cd2 Long Phelp 2019 225 10
ef3 Pam Trace 2022 15 15
;
proc report data=have;
columns ("First Name" firstname) ("Last Name" lastname) ("Sales" year,sales) ("Debt" year,debt);
define firstname/group ' ' order=data;
define lastname/group ' ' order=data;
define sales/sum ' ';
define year/across ' ';
define debt/sum ' ';
run;
Please test your DATA step code before providing it to make sure it works. Testing code to make sure it works is a good thing! Your code did not work. I had to modify it to get it to work, please next time you get it to work before posting it.
Slight improvement to my earlier code:
data have;
length fullname $ 40;
input Id $ FirstName $ LastName $ year sales debt;
fullname=catx(', ',lastname,firstname);
cards;
ab1 Sam Tim 2020 100 0
ab1 Sam Tim 2021 50 70
cd2 Long Phelp 2019 225 10
ef3 Pam Trace 2022 15 15
;
proc report data=have;
columns ("Name" fullname) ("Sales" year,sales) ("Debt" year,debt);
define fullname/group ' ' order=data;
define sales/sum ' ';
define year/across ' ';
define debt/sum ' ';
run;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.