/*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!
... View more
