SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Managing data

Reply
Contributor
Posts: 72

Managing data

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.

Super User
Posts: 19,780

Re: Managing data

Posted in reply to Jahanzaib

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. 

Contributor
Posts: 72

Re: Managing data

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

Attachment
Respected Advisor
Posts: 4,173

Re: Managing data

Posted in reply to Jahanzaib

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;
Contributor
Posts: 72

Re: Managing data

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

Attachment
Super User
Posts: 7,771

Re: Managing data

Posted in reply to Jahanzaib

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 5 replies
  • 618 views
  • 3 likes
  • 4 in conversation