use macro to import multiple excel files?
in [SAS]
|
From: demin on 14 May 2010 09:07 how can i use macro to import multiple excel files? I cannot define array inside the macro, so I use data _NULL_, but it doesn't work. Also I try to use Sql to create the list, but I cannot locate one by one, like array A[1]. Could you please check a little bit? thanks a lot. %let nFiles=4; %let foldData='Data'; %let fold2Entry='2Entry'; %let fold2Score='2ScoreCode'; %let foldCountry='07 Jordan'; %macro importData(path,table); proc import datafile=&path out=&table replace; getnames=yes; run; proc sort data=&table; by IDSAMPLE; run; %mend; data _NULL_; array XLfile[4] $ Locator filter Booklet_1 Booklet_2; do i=1 to &nFiles; %importData("C:\...\data\"+&foldCountry+"\data_ori\Data\"+XLfile[i] +".xls",XLfile[i]); end; run;
From: Tom Abernathy on 14 May 2010 10:27 I do not see any source for the list of files in this program. If you already have that in a dataset then add a SET statement to your data _null_ step. You cannot just call a macro in the middle of the data _null_ step as you have done. That will merely cause the macro to generate its text (code) BEFORE the data step runs. You could just call the macro by using the CALL EXECUTE statement. Assuming you already have a SAS dataset with the four variables LOCATOR FILTER BOOKLET_1 and BOOKLET_2 then you could try code like this to generate four import calls for each observation in your dataset. data _NULL_; array XLfile[4] $ Locator filter Booklet_1 Booklet_2; set FILELIST ; do i=1 to dim(xlfile); call execute('%importData("C:\...\data\&foldCountry\data_ori\Data \' || trim(XLfile[i]) || '.xls",' || XLfile[i] || ')' ); end; run; On May 14, 9:07 am, demin <qdmi...(a)gmail.com> wrote: > how can i use macro to import multiple excel files? I cannot define > array inside the macro, so I use data _NULL_, but it doesn't work. > Also I try to use Sql to create the list, but I cannot locate one by > one, like array A[1]. Could you please check a little bit? thanks a > lot. > > %let nFiles=4; > %let foldData='Data'; > %let fold2Entry='2Entry'; > %let fold2Score='2ScoreCode'; > %let foldCountry='07 Jordan'; > > %macro importData(path,table); > proc import datafile=&path > out=&table > replace; > getnames=yes; > run; > proc sort data=&table; by IDSAMPLE; run; > %mend; > > data _NULL_; > array XLfile[4] $ Locator filter Booklet_1 Booklet_2; > do i=1 to &nFiles; > %importData("C:\...\data\"+&foldCountry+"\data_ori\Data\"+XLfile[i] > +".xls",XLfile[i]); > end; > run;
From: Amar Mundankar on 14 May 2010 10:36 On May 14, 6:07 pm, demin <qdmi...(a)gmail.com> wrote: > how can i use macro to import multiple excel files? I cannot define > array inside the macro, so I use data _NULL_, but it doesn't work. > Also I try to use Sql to create the list, but I cannot locate one by > one, like array A[1]. Could you please check a little bit? thanks a > lot. > > %let nFiles=4; > %let foldData='Data'; > %let fold2Entry='2Entry'; > %let fold2Score='2ScoreCode'; > %let foldCountry='07 Jordan'; > > %macro importData(path,table); > proc import datafile=&path > out=&table > replace; > getnames=yes; > run; > proc sort data=&table; by IDSAMPLE; run; > %mend; > > data _NULL_; > array XLfile[4] $ Locator filter Booklet_1 Booklet_2; > do i=1 to &nFiles; > %importData("C:\...\data\"+&foldCountry+"\data_ori\Data\"+XLfile[i] > +".xls",XLfile[i]); > end; > run; Hi Demin, If you could store the filenames in one macro variable instead of Array, then following code can be of your help. %let nFiles=4; %let foldData='Data'; %let fold2Entry='2Entry'; %let fold2Score='2ScoreCode'; %let foldCountry='07 Jordan'; %macro importData(path,table); proc import datafile=&path out=&table replace; getnames=yes; run; proc sort data=&table; by IDSAMPLE; run; %mend; %let filenames = Locator filter Booklet_1 Booklet_2; %MYIMPORT(NAMES); %do i=1 %to &nFiles; %LET NAME_TEMP = %SCAN(&NAMES, &I); %PUT &NAME_TEMP; %importData("C:\...\data\"+&foldCountry+"\data_ori\Data\"+&i. +".xls",&i.); %end; run; %myimport(&filenames) Thanks and Regards, Amar Mundankar.
From: demin on 14 May 2010 13:52 On May 14, 10:36 am, Amar Mundankar <amarmundan...(a)gmail.com> wrote: > On May 14, 6:07 pm, demin <qdmi...(a)gmail.com> wrote: > > > > > > > how can i use macro to import multiple excel files? I cannot define > > array inside the macro, so I use data _NULL_, but it doesn't work. > > Also I try to use Sql to create the list, but I cannot locate one by > > one, like array A[1]. Could you please check a little bit? thanks a > > lot. > > > %let nFiles=4; > > %let foldData='Data'; > > %let fold2Entry='2Entry'; > > %let fold2Score='2ScoreCode'; > > %let foldCountry='07 Jordan'; > > > %macro importData(path,table); > > proc import datafile=&path > > out=&table > > replace; > > getnames=yes; > > run; > > proc sort data=&table; by IDSAMPLE; run; > > %mend; > > > data _NULL_; > > array XLfile[4] $ Locator filter Booklet_1 Booklet_2; > > do i=1 to &nFiles; > > %importData("C:\...\data\"+&foldCountry+"\data_ori\Data\"+XLfile[i] > > +".xls",XLfile[i]); > > end; > > run; > > Hi Demin, > If you could store the filenames in one macro variable instead of > Array, then following code can be of your help. > > %let nFiles=4; > %let foldData='Data'; > %let fold2Entry='2Entry'; > %let fold2Score='2ScoreCode'; > %let foldCountry='07 Jordan'; > > %macro importData(path,table); > proc import datafile=&path > out=&table > replace; > getnames=yes; > run; > proc sort data=&table; by IDSAMPLE; run; > %mend; > > %let filenames = Locator filter Booklet_1 Booklet_2; > > %MYIMPORT(NAMES); > %do i=1 %to &nFiles; > %LET NAME_TEMP = %SCAN(&NAMES, &I); > %PUT &NAME_TEMP; > %importData("C:\...\data\"+&foldCountry+"\data_ori\Data\"+&i. > +".xls",&i.); > %end; > run; > > %myimport(&filenames) > > Thanks and Regards, > Amar Mundankar.- Hide quoted text - > > - Show quoted text - THanks a lot, i've learned.
|
Pages: 1 Prev: How to Pump $1,000s in CASH & Checks to your door. Next: Retain -- Data Structure |