DATA Step, Macro, Functions and more

Union using Proc SQL - Macro Variables

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

Union using Proc SQL - Macro Variables

[ Edited ]

 

 


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

 

 


Accepted Solutions
Solution
‎02-20-2017 01:32 PM
Respected Advisor
Posts: 4,930

Re: Union using Proc SQL - Macro Variables

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;
PG

View solution in original post


All Replies
Super Contributor
Posts: 474

Re: Union using Proc SQL - Macro Variables

[ Edited ]

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

 

Contributor
Posts: 42

Re: Union using Proc SQL - Macro Variables

Posted in reply to DanielSantos

Thanks a lot man for your replay

Solution
‎02-20-2017 01:32 PM
Respected Advisor
Posts: 4,930

Re: Union using Proc SQL - Macro Variables

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;
PG
Contributor
Posts: 42

Re: Union using Proc SQL - Macro Variables

Thanks Man!! Smiley Happy 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,

 

 

 

 

 

 

 

Respected Advisor
Posts: 4,930

Re: Union using Proc SQL - Macro Variables

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

 

PG
Contributor
Posts: 42

Re: Union using Proc SQL - Macro Variables

Thanks a lot for easy explanation!!

Contributor
Posts: 42

Re: Union using Proc SQL - Macro Variables

[ Edited ]

 

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 230 views
  • 0 likes
  • 3 in conversation