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

Files need some transpose functions needs your help. Thanks

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

Files need some transpose functions needs your help. Thanks

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 Smiley Sad

i hope you can do it in just few steps

thanks a lot you gays are great

Regards

Attachment

Accepted Solutions
Solution
‎12-22-2014 01:34 PM
Super User
Super User
Posts: 6,495

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

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

View solution in original post


All Replies
Super User
Super User
Posts: 6,495

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

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

Contributor
Posts: 58

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

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

Respected Advisor
Posts: 4,640

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

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

PG

PG
Contributor
Posts: 58

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

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

Regards

Solution
‎12-22-2014 01:34 PM
Super User
Super User
Posts: 6,495

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

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

Contributor
Posts: 58

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

THanks a lot. You are the best :smileygrin:

Respected Advisor
Posts: 4,640

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

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 COLSmiley Happy;    /* 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;

PG

PG
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 578 views
  • 3 likes
  • 3 in conversation