nested macro help

[woo]

Hi friends,

i am using following code to read 2 .xls files together from directory called 'woo' and i want to rename all variable in 2 xls files at same time by putting prefix (_glob) infront of all variables in xls sheet...

/*reading two xls files all together from one directory*/

/*i think this code is fine i just want to add two more macro parameter along with 'dir' and 'ext' in below logic then give them value same variable as 2 xls file has and then rename all 2 xls files variable altogether putting "globe_" as prefix---so wonder if i can do that*/

options mlogic mprint sybolgen;

/*woo directory has two excel files)

libname woo 'f:\woo';

%macro drive (dir,ext);

%let filrf=mydir;

%let rc=%sysfunc(filename (filrf,&dir));

%let did=%sysfunc(dopen(&filrf));

%let memcnt=%sysfunc(dnum(&did));

%do i=1 %to &memcnt;

%let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);

%if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;

%let dslist=;

%if (%superq(ext) ne and %qupcase (&name)=%qupcase(&ext)) or

(%superq(ext)=and %superq(name)ne %then %do;

%let file=%qsysfunc(dread(&did,&i));

%let dslist=&dslist %scan(&file,1,.);

proc import datafile="&dir.%unquote(&file)" out=%scan(%unquote(&file),1,.)

dbms=csv;

guessingrows=100;

getnames=yes;

run;

%end;

%end;

%end;

%let rc=%sysfunc(dclose(&did));

%mend drive;

%drive(f:\woo\,csv);

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

/*now to apply "globe_" as prefix for all variable in 2 .xls files, i am planning to add two more parameters 'list' and 'prefix' in above code like as mentioned below*/

options mlogic mprint sybolgen;

/*woo directory has two excel files 'test1.xls' and test2.xls'*/

libname woo 'f:\woo';

%macro drive (dir,ext, list, prefix);

/*

list=date air name sex age height weight,

prefix=_globe

*/

Thanks!

[sas_9]

this would be great outcome... reading all .xls files at once and rename all variables in all .xls files together by applying same prefix to all variable...

good imagination...waiting for someone come up with code...

[Reeza]

You also need to control for types and ensure that var36 in file1 and var36 in file2 have the same type (char/num) after being read in from excel which is the harder part in my opinion.

Can you assume that the files are the same and all variables are in the same order?

[sas_9]

woo, first of all your initial code will not work. since you are talking about reading .xls files and you are mentioning csv in option...so you need two changes at first glance, change at two places, %drive(f:\woo\,xls); and dbms=xls;

step2: you have to add logic to rename all xls file variable...

/*you new code*/

/*i didn't add logic to rename but just made two changes*/

options mlogic mprint sybolgen;

/*woo directory has two excel files)

libname woo 'f:\woo';

%macro drive (dir,ext);

%let filrf=mydir;

%let rc=%sysfunc(filename (filrf,&dir));

%let did=%sysfunc(dopen(&filrf));

%let memcnt=%sysfunc(dnum(&did));

%do i=1 %to &memcnt;

%let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);

%if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;

%let dslist=;

%if (%superq(ext) ne and %qupcase (&name)=%qupcase(&ext)) or

(%superq(ext)=and %superq(name)ne %then %do;

%let file=%qsysfunc(dread(&did,&i));

%let dslist=&dslist %scan(&file,1,.);

proc import datafile="&dir.%unquote(&file)" out=%scan(%unquote(&file),1,.)

dbms=xls;

guessingrows=100;

getnames=yes;

run;

%end;

%end;

%end;

%let rc=%sysfunc(dclose(&did));

%mend drive;

%drive(f:\woo\,xls);

[woo]

Thanks Sandy - i got that piece...

@ Reeza - i have two xls files in woo directory with different number of variables and different number of observation...

Thanks!

[Reeza]

This paper covers how to add a prefix and rename variables dynamically.

http://support.sas.com/resources/papers/proceedings09/075-2009.pdf

[Patrick]

Here a code example of how this renaming bit could work. You just need to work this into your macro (or have it in a second macro which you then call from your first macro).

data work.Excel1;
  set sashelp.class;
  'Just another column'n=name;
run;

data work.Excel2;
  set sashelp.class;
  'Just another column'n=age;
run;

proc datasets lib=work memtype=(data view) nolist nowarn;
  delete v_Excel_Columns;
quit;

proc sql;
  create view v_Excel_Columns as
    select libname, memname, name
      from dictionary.columns
        where libname='WORK' and memname in ('EXCEL1','EXCEL2')
          order by memname
  ;
quit;

filename codegen temp;

data _null_;
/*  file print;*/
  file codegen;
  set v_Excel_Columns;
  by memname;

  if first.memname then
    do;
      put 'proc datasets lib=' libname 'nolist;';
      put '  modify ' memname ';';
    end;

  new_name=cats("'_glob",substrn(name,1,27),"'n");
  put "    rename '" name +(-1) "'n = " new_name ";" ;

  if last.memname then
    do;
      put '  run;';
      put 'quit;' /;
    end;
run;

proc datasets lib=work memtype=(data view) nolist nowarn;
  delete v_Excel_Columns;
quit;

%include codegen / source2;

[woo]

/*thanks for your note Reeza - its Awesome - but here i am thinking something different or may be something impossible - i am new in this world so i don't know...*/

/*may be you guys can make me understand*/

/*okay here let me clarify myself little bit more...I come up with this renmaing process and it works absolutely fine - no error no warning message...*/

/*have two .xls files in woo directory - test1.xls (set sashelp.air) and test3.xls (set sashelp.class)*/

libname woo 'f:\woo';

%macro drive (dir,ext);

%let filrf=mydir;

%let rc=%sysfunc(filename (filrf,&dir));

%let did=%sysfunc(dopen(&filrf));

%let memcnt=%sysfunc(dnum(&did));

%do i=1 %to &memcnt;

%let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);

%if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;

%let dslist=;

%if (%superq(ext) ne and %qupcase (&name)=%qupcase(&ext)) or

(%superq(ext)=and %superq(name)ne %then %do;

%let file=%qsysfunc(dread(&did,&i));

%let dslist=&dslist %scan(&file,1,.);

proc import datafile="&dir.%unquote(&file)" out=%scan(%unquote(&file),1,.)

dbms=xls

replace;

guessingrows=100;

getnames=yes;

run;

%end;

%end;

%end;

%let rc=%sysfunc(dclose(&did));

%mend drive;

%drive(f:\woo\,xls);

%macro rename (list, prefix);

%local i name2;

%do i=1 % %sysfunc(countw(&list));

%let name2=%scan(&list,&i);

&name2=&perfix.&name2

%end;

%mend rename;

data test2;

set test1;

rename %rename(date air, globe_);

run;

data test4;

set test3;

rename %rename(name sex age height weight, globe_);

run;

/*-----------works fine - test2.sas7bdat and test4.sas7bdat come up with prefix globe_ for all variables --------------*/

BUT,

I am thinking if there is another way we can use only one data step (instead of two here and may be more if we have more xls files to rename) and include all .xls files and rename them all by applying prefix "globe_" ...i am thinking to incldue two or more rename statement in one data step like this;

/*but i know that below code will not work since SAS will confuse what "SET" statement TEST2 will use and what "SET" statement TEST4 will use so this will not work - but i am thinking if we cab write only data step somehow... 🙂 ..

data test2 test4;

set test1 test3;

rename %rename(date air, globe_);

rename %rename(name sex age height weight, globe_);

run;

Thanks All!