topic Union using Proc SQL - Macro Variables in SAS Programming

Union using Proc SQL - Macro Variables

Jcorti — Mon, 20 Feb 2017 17:15:14 GMT


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

Re: Union using Proc SQL - Macro Variables

DanielSantos — Mon, 20 Feb 2017 17:20:01 GMT

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

Re: Union using Proc SQL - Macro Variables

PGStats — Mon, 20 Feb 2017 17:35:43 GMT

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;

Re: Union using Proc SQL - Macro Variables

Jcorti — Mon, 20 Feb 2017 18:32:08 GMT

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,

Re: Union using Proc SQL - Macro Variables

Jcorti — Mon, 20 Feb 2017 18:34:06 GMT

Thanks a lot man for your replay

Re: Union using Proc SQL - Macro Variables

PGStats — Mon, 20 Feb 2017 19:22:25 GMT

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

Re: Union using Proc SQL - Macro Variables

Jcorti — Mon, 20 Feb 2017 21:26:17 GMT

Thanks a lot for easy explanation!!

Re: Union using Proc SQL - Macro Variables

Jcorti — Tue, 21 Feb 2017 22:35:58 GMT

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,