BookmarkSubscribeRSS Feed
Jahanzaib
Quartz | Level 8

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.

5 REPLIES 5
Reeza
Super User

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
Quartz | Level 8

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

Patrick
Opal | Level 21

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
Quartz | Level 8

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
Super User

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.

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to connect to databases in SAS Viya

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.

Discussion stats
  • 5 replies
  • 1990 views
  • 3 likes
  • 4 in conversation