DATA Step, Macro, Functions and more

Appending dataset in Macro loop

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Appending dataset in Macro loop

Hi , 

 

I want to append the dataset A_1 , A_2 , A_3 to a New dataset called APPEND_DT .Trying to pass the parameters through macro do loop , but setting the dataset  for each time APPEND_DT is overwritten . 

 

Help me in this ! 

 

Program:


/** Sample DATASET 1 **/
DATA A_1;
input ID ;
datalines;
1
2
3
4
;
RUN;

/** Sample DATASET 2 **/

DATA A_2;
input ID ;
datalines;
5
6
;
RUN;

/** Sample DATASET 3 **/

DATA A_3;
input ID ;
datalines;
7
8
9
;
RUN;


/* Appending the above dataset using Do loop */

%MACRO TEST;

           %DO i=1 %TO 3 ;

                    PROC PRINT DATA = A_&i.;
                   RUN;

                   DATA APPEND_DT;
                   SET A_&i.;
                   RUN;
%END;

PROC PRINT DATA = APPEND_DT;
RUN;

%MEND;

%TEST;

 

 

Desired OP:

 

 

1

2

3

4

5

6

7

8

9

 


Accepted Solutions
Solution
‎11-15-2016 08:50 AM
Trusted Advisor
Posts: 1,568

Re: Appending dataset in Macro loop

[ Edited ]
Posted in reply to user24feb

PROC APPEND assumes that base table and new data table have same variables (otherwise use option FORCE).

If base table does not exist, the proc will copy first appaended table to be the base.

 

In your example, you created 3 tables A_1 A_2 A_3;

So your program should be:

proc datasets lib=work nolist;  /* to be done first time only */
       delete append_dt;    /* do not care if you get message that table does not exist */
quit; run;

%macro append_all;
%do i=1 %to 3;
proc append base=append_dt data=A_&i; run;
%end;
%mend append_all;
%append_all;
proc print data=append_dt; run;

 

View solution in original post


All Replies
Super Contributor
Posts: 340

Re: Appending dataset in Macro loop

Posted in reply to monikka1991_gmail_com
/* Appending the above dataset using Do loop */

%MACRO TEST;

           %DO i=1 %TO 3 ;

  %If &i.=1 %Then %Do;
  Data Append_DT;
    Set A_&i.;
  Run;
  %End;
  %Else %Do;
  Proc Append Base=Append_DT Data=A_&i.;
  Run;
  * OR:;
/*  Data Append_DT;*/
/*    Set Append_DT A_&i.;*/
/*  Run;*/
  %End;
%END;

PROC PRINT DATA = APPEND_DT;
RUN;

%MEND;

%TEST;
Solution
‎11-15-2016 08:50 AM
Trusted Advisor
Posts: 1,568

Re: Appending dataset in Macro loop

[ Edited ]
Posted in reply to user24feb

PROC APPEND assumes that base table and new data table have same variables (otherwise use option FORCE).

If base table does not exist, the proc will copy first appaended table to be the base.

 

In your example, you created 3 tables A_1 A_2 A_3;

So your program should be:

proc datasets lib=work nolist;  /* to be done first time only */
       delete append_dt;    /* do not care if you get message that table does not exist */
quit; run;

%macro append_all;
%do i=1 %to 3;
proc append base=append_dt data=A_&i; run;
%end;
%mend append_all;
%append_all;
proc print data=append_dt; run;

 

Super User
Posts: 19,815

Re: Appending dataset in Macro loop

Posted in reply to monikka1991_gmail_com

Yes, it will recreate a new dataset each time that's only the current values. It needs to be something like:

 

data want;

set want a_1;

run; 

 

But in this case want has to already exist. A common workaround is to say if n=1 then 

 

data want; 

set a_1;

run;

 

Otherwise:

 

data want;

set want A_&i;

run; 

 

 

Better yet, avoid macros entirely unless you enjoy obfuscating code:

 

data append_dt;

   Set a_1 - a_3;

run;

Super User
Super User
Posts: 7,958

Re: Appending dataset in Macro loop

Posted in reply to monikka1991_gmail_com

Sorry, I am not seeing why you would need macro?  Something simple like:

data want;
  set append_dt a_:;
run;

Will do the job without all that kurfuffle.  

Trusted Advisor
Posts: 1,568

Re: Appending dataset in Macro loop

@RW9 your note is interesting.

 

I have checked it on SAS UE and got ERROR pointint at the  :

 

56 options nonotes;
57 data test1; x=1; output; run;
58 data test2; x=2; output; run;
59
60 proc append base=all_test
61        data = test:;
                           _
                          22
                          200
ERROR: File WORK.TEST.DATA does not exist.
ERROR 22-322: Syntax error, expecting one of the following: ;, (, APPENDVER, APPENDVERSION, BASE, CREATE, DATA, FORCE, GETSORT,
NEW, NOWARN, OUT.
ERROR 200-322: The symbol is not recognized and will be ignored.
62 run cancel;
WARNING: The procedure was not executed at the user's request.

 

 

Super User
Super User
Posts: 7,958

Re: Appending dataset in Macro loop

Hi,

 

I assumed (incorrectly it appears) that lists would work in that.  Its seems not, and that makes sense as it is a procedure with parameters.  Of course it works fine in a datastep, so the same thing:

data test1;
  a=1;
run;
data test2;
  a=2;
run;
data want;
  set test:;
run;
☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 2616 views
  • 5 likes
  • 5 in conversation