Union using Proc SQL - Macro Variables

[Jcorti]

****************************************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,

[DanielSantos]

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

[Jcorti]

Thanks a lot man for your replay

[PGStats · Accepted solution]

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;

[Jcorti]

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,

[PGStats]

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")

[Jcorti]

Thanks a lot for easy explanation!!

[Jcorti]

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