****************************************Example*****************************************************
%macro import_loop;
%let outobs=max;
%do i1=0 %to 24;
%let i = %sysfunc( putn(&i1,z2.));
proc import out = testv1_&i
datafile = '/TESTING/DVR/DVR_V1_PROD_01182017.xls'
dbms = xls ;
sheet ="M&i._ACCT_BAL_AM";
getnames = yes;
run;
proc sql outobs=&outobs;
create table M&i._ACCT_BALANCE_AM
as
select "M_ACCT_BAL_AM_&i." as M_ACCT_BAL_AM_&i.
,a.*
FROM testv1_&i a
;
%end;
%mend import_loop;
%import_loop;
Hi Team,
Somone of you have you ever used **Union with Proc SQL** using macrovariables??
With the macro shown above I created 25 tables.
I want to create in the last step only one table which contains 25 tables
previously created using **Union of Proc SQL with macrovariables**..
How can I do it using Macrovariables in a simple way similar that I created the 25 tables??
Thanks and Regards,
Use a dataset list within a set statement to read all datasets and option indsname to get their names:
%macro import_loop;
%do i1=0 %to 24;
%let i = %sysfunc( putn(&i1,z2.));
proc import
out = testv1_&i
datafile = '/TESTING/DVR/DVR_V1_PROD_01182017.xls'
dbms = xls ;
sheet ="M&i._ACCT_BAL_AM";
getnames = yes;
run;
%end;
data allMyData;
length fromSheet $32;
set testv1_: indsname=name;
fromSheet = cats("M", scan(name, 3, "._"), "_ACCT_BAL_AM");
run;
%mend import_loop;
%import_loop;
Hi.
Why not using PROC APPEND?
%macro import_loop;
proc datasets lib=WORK nolist;
delete M_ALL;
quit; * delete first;
%let outobs=max;
%do i1=0 %to 24;
%let i = %sysfunc( putn(&i1,z2.));
proc import out = testv1_&i
datafile = '/TESTING/DVR/DVR_V1_PROD_01182017.xls'
dbms = xls ;
sheet ="M&i._ACCT_BAL_AM";
getnames = yes;
run;
proc sql outobs=&outobs;
create table M&i._ACCT_BALANCE_AM
as
select "M_ACCT_BAL_AM_&i." as M_ACCT_BAL_AM_&i.
,a.*
FROM testv1_&i a
;
proc append base=M_ALL data=M&i._ACCT_BALANCE_AM force;
run; * append;
%end;
%mend import_loop;
%import_loop;
Daniel Santos @ www.cgd.pt
Thanks a lot man for your replay
Use a dataset list within a set statement to read all datasets and option indsname to get their names:
%macro import_loop;
%do i1=0 %to 24;
%let i = %sysfunc( putn(&i1,z2.));
proc import
out = testv1_&i
datafile = '/TESTING/DVR/DVR_V1_PROD_01182017.xls'
dbms = xls ;
sheet ="M&i._ACCT_BAL_AM";
getnames = yes;
run;
%end;
data allMyData;
length fromSheet $32;
set testv1_: indsname=name;
fromSheet = cats("M", scan(name, 3, "._"), "_ACCT_BAL_AM");
run;
%mend import_loop;
%import_loop;
Thanks Man!! 🙂 Actually was not needed proc sql I removed that part;
Could you please explain me what those statements do?
set testv1_: indsname=name;
fromSheet = cats("M", scan(name, 3, "._"), "_ACCT_BAL_AM");
My guessing is the new variable **fromsheet" is selecting all variables that contains **_ACCT_BAL_AM**
When I am creating **fromSheet variable** can I include in addition of _ACCT_BAL_AM another variables??
I am asking because in my excel file DVR_V1_PROD_01182017 has more variables like **M&i._ACT_PYMT_AMT**
Thanks a lot,
set testv1_: reads all datasets in the WORK library with names starting with testv1_
indsname=name creates a variable named name that will be assigned the name of the dataset being read (eg "WORK.TESTV1_04")
scan(name, 3, "._") extracts the number from the dataset name (eg "04")
cats("M", scan(name, 3, "._"), "_ACCT_BAL_AM") rebuilds the Excel sheet name (eg "M04_ACCT_BAL_AM")
Thanks a lot for easy explanation!!
Hi again,
Do you know if I can reference more variables here??
Asking because I need to cosolidate into:
data allMyData;
length fromSheet $32;
set testv1_: indsname=name;
fromSheet = cats("M", scan(name, 3, "._"), "_ACCT_BAL_AM");
run;
more macrovariables highlighted in black below:
%do i1=0 %to 24;
%let i = %sysfunc(putn(&i1,z2.));
%let cons=consolidated;
%let var1=M&i._ACCT_BALANCE_AM;
%let var2=M&i._ACCT_CREDIT_LIMIT_AM;
%let var3=M&i._ACTUAL_PYMT_AM;
%let var4=M&i._ACCT_PAYMENT_AM;
Thanks a lot in advance,
JC
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.