Managing data

[Jahanzaib]

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.

[Reeza]

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. 

[Jahanzaib]

i have added the file both in CSV and excel format in the attached zip folder. 

[Patrick]

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;

[Jahanzaib]

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

[Kurt_Bremser]

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.