topic Re: Managing data in SAS Data Management

Managing data

Jahanzaib — Sun, 24 Jul 2016 03:07:50 GMT

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.

Re: Managing data

Reeza — Sun, 24 Jul 2016 03:30:06 GMT

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.

Re: Managing data

Jahanzaib — Sun, 24 Jul 2016 03:45:26 GMT

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

Re: Managing data

Patrick — Sun, 24 Jul 2016 04:35:32 GMT

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;

Re: Managing data

Jahanzaib — Sun, 24 Jul 2016 14:36:14 GMT

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

Re: Managing data

Kurt_Bremser — Mon, 25 Jul 2016 07:10:40 GMT

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.