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

Managing data

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: 23,342

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. 

Posts: 72

Re: Managing data

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

Respected Advisor
Posts: 4,692

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);
    Amount=input(years(_i),?? best32.);;

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

Super User
Posts: 9,924

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;
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

proc transpose data=have out=want (rename=(_name_=year));
by company;
id variable;
var _numeric_;

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
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 5 replies
  • 4 in conversation