I have data in this form
companyname variable Year1 Year2 year3 Year4 Year5
Abc assets
Abc liabilities
-----
I want the data in the form
Companyname Year Assets Liabilities Deficit
Abc year1
Abc Year2
Can anyone here guide me how can i convert data in first format to second?
I am attaching the excel file here. Sheet1 of excel file is the original data. i want my data in the format of sheet2.
Look at proc transpose.
PS I don't download XLSX files and many others don't as well. It's best to be as detailed as possible in your question.
i have added the file both in CSV and excel format in the attached zip folder.
Below code brings your source data into a shape which is favourable for quite a few PROC's - eg. Proc Report for what I believe you're trying to do.
data work.phil(keep=companyname credeb year amount);
infile 'c:\test\phil.csv' firstobs=2 lrecl=255 dlm=',' truncover dsd;
input CompanyName :$50. Var :$30. (YearIn2000 - YearIn2015) (:$20.);
if compress(upcase(var)) in ('TOTALASSETS','TOTALDEBT');
if compress(upcase(var))='TOTALASSETS' then CreDeb='C';
else CreDeb='D';
array years {*} YearIn:;
do _i=1 to dim(years);
Year=input(compress(vname(years(_i)),,'kd'),best32.);
Amount=input(years(_i),?? best32.);;
output;
end;
run;
Thanks Patrick for your kindly reply.
Sorry for disturbing you again. I have sent you the wrong file.
I am again attaching the file.
Sheet1 in this data set is data available with me.
Sheet2 is the form of data set that i want.
Thanks in anticipation
The basic method for using proc transpose for such a problem looks like this:
data have;
input company$ variable$ val2000 val2001 val2002;
cards;
a x 1 2 3
a y 4 5 6
a z 7 8 9
b x 11 12 13
b y 14 15 16
b z 17 18 19
;
run;
proc transpose data=have out=want (rename=(_name_=year));
by company;
id variable;
var _numeric_;
run;
Make sure that your "have" dataset has values in "variable" that comply to SAS naming conventions, otherwise you will have a hard time dealing with the variable names.
In my given example, you might want to run a second data step that converts the "year" variable to numeric.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.