BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
raqthesolid
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

7 REPLIES 7
Tom
Super User Tom
Super User

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

raqthesolid
Quartz | Level 8

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

PGStats
Opal | Level 21

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

PG

PG
raqthesolid
Quartz | Level 8

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

Regards

Tom
Super User Tom
Super User

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

raqthesolid
Quartz | Level 8

THanks a lot. You are the best :smileygrin:

PGStats
Opal | Level 21

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;

PG

PG

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 7 replies
  • 1321 views
  • 3 likes
  • 3 in conversation