topic Re: Appending dataset in Macro loop in SAS Programming

Appending dataset in Macro loop

monikka1991 — Tue, 15 Nov 2016 06:13:11 GMT

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:

Re: Appending dataset in Macro loop

user24feb — Tue, 15 Nov 2016 06:30:03 GMT

/* 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;

Re: Appending dataset in Macro loop

Reeza — Tue, 15 Nov 2016 06:50:33 GMT

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;

Re: Appending dataset in Macro loop

Shmuel — Tue, 15 Nov 2016 09:46:19 GMT

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;

Re: Appending dataset in Macro loop

RW9 — Tue, 15 Nov 2016 13:25:17 GMT

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.

Re: Appending dataset in Macro loop

Shmuel — Tue, 15 Nov 2016 14:55:15 GMT

@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.

Re: Appending dataset in Macro loop

RW9 — Tue, 15 Nov 2016 15:20:50 GMT

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;