topic Re: Files need some transpose functions needs your help. Thanks in SAS Data Management

Files need some transpose functions needs your help. Thanks

raqthesolid — Mon, 22 Dec 2014 15:32:53 GMT

HI friends

i have loaded two files one is the file name=DVM1 which i want to convert into file similar to other file named=em

i do not know to use the sas so previously i converted this manually in excel which took me ages

i hope you can do it in just few steps

thanks a lot you gays are great

Regards

Re: Files need some transpose functions needs your help. Thanks

Tom — Mon, 22 Dec 2014 15:40:13 GMT

Sure we can help, but you need to explain the rules for the transformation.

Re: Files need some transpose functions needs your help. Thanks

PGStats — Mon, 22 Dec 2014 16:54:44 GMT

If you were trying to include the example files with your message, try to ZIP them before inclusion.

Re: Files need some transpose functions needs your help. Thanks

raqthesolid — Mon, 22 Dec 2014 17:35:46 GMT

Thanks TOM,

I just want to convert some of the rows in to the columns;

for example see the first bank in the excel file named=Raiffeisen Wohnbaubank

i need the data in the form

Bank Year ROAA ROAE EQ TA PFT ------------------

Raiffeisen Wohnbaubank 2012 ---- --- --- --- --- ---

Raiffeisen Wohnbaubank 2011 ---- --- --- --- --- ---

Raiffeisen Wohnbaubank 2010

AND SO ON TILL 1999

years are given in the first row;

Thanks a lot for you valuable time and helping me.

Regards

Re: Files need some transpose functions needs your help. Thanks

raqthesolid — Mon, 22 Dec 2014 17:37:30 GMT

Thanks PGStates i will remember this and i have changed the files according to your directions.

Regards

Re: Files need some transpose functions needs your help. Thanks

Tom — Mon, 22 Dec 2014 18:34:26 GMT

So now that we can see the files it does not look that hard. There are some columns with messy names. For example with extra spaces and one or more with 20121 for the "year" part of the column name.

%let path=C:\Users\abernathyt\Downloads;

%let fname=DVM1.xls;

* Convert EXCEL to SAS ;

proc import datafile="&path\&fname" out=have ;

run;

* Add Unique RECNO to preserve row identifiers ;

data add_recno;

length recno 8 ;

set have ;

recno + 1;

run;

* Transpose ;

proc transpose data=add_recno out=flip ;

by recno bank_name country_code ;

var _all_;

run;

* Pull the YEAR value out of the column names and convert the character COL1 into numeric COL2;

data flop ; set flip ;

if lowcase(_name_) in ('bank_name','country_code','recno') then delete;

yr = scan(_name_,-1,'_');

_name_ = substr(_name_,1,length(_name_)-6);

do while (substr(_name_,length(_name_))='_'); _name_=substr(_name_,1,length(_name_)-1); end;

col2 = input(left(col1),??comma32.);

run;

* Re-sort to make sure YRs are in order ;

proc sort data=flop;

by recno bank_name country_code yr ;

run;

* Re-transpose ;

proc transpose data=flop out=want(drop=_name_) ;

by recno bank_name country_code yr ;

id _name_ ;

var col2;

run;

proc print data=want(obs=9) width=min;

run;

Obs recno Bank_Name Country_code yr ROAA ROAE EQ TA PFT IE NIM EQ_TA OH OBS LG

1 1 Raiffeisen Wohnbaubank AT 1999 . . . . . . . . . . .

2 1 Raiffeisen Wohnbaubank AT 2000 . . . . . . . . . . .

3 1 Raiffeisen Wohnbaubank AT 2001 0.142 8.065 5464.00 310919.18 705.03 14188.77 0.372 1.757 440.65 . .

4 1 Raiffeisen Wohnbaubank AT 2002 0.098 6.452 6501.68 488464.79 629.19 19505.03 0.296 1.331 629.19 . .

5 1 Raiffeisen Wohnbaubank AT 2003 0.102 9.449 8209.14 891260.41 1136.65 31826.22 0.275 0.921 884.06 . .

6 1 Raiffeisen Wohnbaubank AT 2004 0.084 10.769 8853.66 1306799.66 1498.31 47945.95 0.229 0.678 1089.68 . .

7 1 Raiffeisen Wohnbaubank AT 2005 0.069 10.606 7904.02 1249070.92 1179.70 47542.08 0.189 0.633 1061.73 . .

8 1 Raiffeisen Wohnbaubank AT 2006 0.072 11.765 9087.32 1549321.78 1448.70 57553.01 0.233 0.587 1185.30 . .

9 1 Raiffeisen Wohnbaubank AT 2007 0.045 8.633 10304.73 2177830.06 1030.47 75077.28 0.197 0.473 1619.31 . .

Re: Files need some transpose functions needs your help. Thanks

PGStats — Mon, 22 Dec 2014 19:01:31 GMT

I suggest you clean up your data before processing. My take:

libname xl Excel "&SASFORUM.\datasets\DVM1.xls" access=readonly;

proc transpose data=xl.'Results$'n out=dvm1Long(drop=_label_);

var _numeric_;

by bank_Name Country_Code notsorted;

run;

data temp;

set dvm1Long;

year = input(substr(_NAME_, prxmatch("/_\d+$/", trim(_NAME_)) + 1), best.);

var = substr(_NAME_, 1, prxmatch("/_+\d+$/", trim(_NAME_)) - 1);

value = mean(of COL:); /* There are up to 7 records for a given Bank_Name and Country_Code, take the average? */

*value = COL1; /* Maybe you only want the first value ? */

keep Bank_Name Country_Code year var value;

run;

proc sql;

create table temp2 as

select Bank_Name, Country_Code, year, var, mean(value) as value

from temp

group by Bank_Name, Country_Code, year, var

order by Bank_Name, Country_Code, year, var;

quit;

proc transpose data=temp2 out=want(drop=_NAME_);

by Bank_Name Country_Code year;

var value;

id var;

run;

Re: Files need some transpose functions needs your help. Thanks

raqthesolid — Tue, 23 Dec 2014 02:13:49 GMT

THanks a lot. You are the best :smileygrin: