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-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 1573 views
  • 3 likes
  • 4 in conversation