topic Proc transpose with multiple var in SAS Programming

Proc transpose with multiple var

sunnyday — Fri, 25 Nov 2022 12:09:06 GMT

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!

Re: Proc transpose with multiple var

PaigeMiller — Fri, 25 Nov 2022 12:42:01 GMT

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.

Re: Proc transpose with multiple var

PaigeMiller — Fri, 25 Nov 2022 12:44:49 GMT

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;

Re: Proc transpose with multiple var

sunnyday — Fri, 25 Nov 2022 13:04:07 GMT

Thanks for the quick reply! I'll take care of the Code from next time.