Dynamic construction of a data step merge statement

Accepted Solution Solved
Reply
Contributor kbk
Contributor
Posts: 29
Accepted Solution

Dynamic construction of a data step merge statement

Hi All,

I want to use a macro that takes a space-delimited variable containing dataset names and merges those datasets. I've made a bit of progress but have become stuck because I also need to use the IN option in the merge statement. Any ideas how to construct this statement such that all I need to input is a list of dataset names?

*some fake datasets for demonstration;

data work.table1;

    input name $ desc $;

    datalines;

a descA

b descB

c descC

d descD

run;

data work.table2;

    input name $ desc $;

    datalines;

b descB

d descD

e descE

f descF

run;

data work.table3;

    input name $ desc $;

    datalines;

b descB

c descC

f descF

g descG

h descH

run;

* a macro variable with tables to merge;

%let tabList = table1 table2 table3;

%put Tables to be merged: &tabList;

*trying a macro approach to construct the data step merge statement;

%macro createMerge(tabList);

    %local mergeStatement;

    %do i = 1 %to %SYSFUNC(COUNTC(&tabList,' '))+1;

        %let table = %scan(&tabList, &i, ' ');

        %let tableIn = &table.&i;

        %let mergeStatement = &mergeStatement &table(in=&tableIn) ;

        %let if&i = if &tableIn then &table  = 'X';

        %put &&&if&i;

    %end;

    %let mergeStatement = merge &mergestatement ;

    %put &mergeStatement;

    data checkListMerge;

        &mergeStatement;

        by name;

        * Not sure how to get away from manually specifying this section;

        &if1;

        &if2;

        &if3;

    run;

%mend createMerge;

%createMerge(&tabList);

This is what I'm trying to construct.

data checkListMerge;

    merge table1(in=a) table2(in=b) table3(in=c);

    by name; *data already sorted;

    if a then Table1 = 'X';

    if b then Table2 = 'X';

    if c then Table3 = 'X';

run;


Accepted Solutions
Solution
‎02-18-2013 01:53 PM
Super User
Super User
Posts: 7,074

Re: Dynamic construction of a data step merge statement

Your code is working too hard.

%macro createMerge(tables);

%local i n;

%let n=%sysfunc(countw(&tables,%str( )));

data checkListMerge;

    merge

%do i=1 %to &n ;

      %scan(&tables,&i,%str( )) (in=_in&i)

%end;

    ;

    by name; *data already sorted;

%do i=1 %to &n ;

    if _in&i then table&i='X';

%end;

run;


%mend createMerge;

Example:

options mprint;

%createMerge(sashelp.class sashelp.class);


MPRINT(CREATEMERGE):   data checkListMerge;

MPRINT(CREATEMERGE):   merge sashelp.class (in=_in1) sashelp.class (in=_in2) ;

MPRINT(CREATEMERGE):   by name;

MPRINT(CREATEMERGE):   *data already sorted;

MPRINT(CREATEMERGE):   if _in1 then table1='X';

MPRINT(CREATEMERGE):   if _in2 then table2='X';

MPRINT(CREATEMERGE):   run;

View solution in original post


All Replies
Solution
‎02-18-2013 01:53 PM
Super User
Super User
Posts: 7,074

Re: Dynamic construction of a data step merge statement

Your code is working too hard.

%macro createMerge(tables);

%local i n;

%let n=%sysfunc(countw(&tables,%str( )));

data checkListMerge;

    merge

%do i=1 %to &n ;

      %scan(&tables,&i,%str( )) (in=_in&i)

%end;

    ;

    by name; *data already sorted;

%do i=1 %to &n ;

    if _in&i then table&i='X';

%end;

run;


%mend createMerge;

Example:

options mprint;

%createMerge(sashelp.class sashelp.class);


MPRINT(CREATEMERGE):   data checkListMerge;

MPRINT(CREATEMERGE):   merge sashelp.class (in=_in1) sashelp.class (in=_in2) ;

MPRINT(CREATEMERGE):   by name;

MPRINT(CREATEMERGE):   *data already sorted;

MPRINT(CREATEMERGE):   if _in1 then table1='X';

MPRINT(CREATEMERGE):   if _in2 then table2='X';

MPRINT(CREATEMERGE):   run;

Contributor kbk
Contributor
Posts: 29

Re: Dynamic construction of a data step merge statement

Your code is working too hard.

It tends to do that :smileyplain:. Which is usually when I come here for help. Thanks a lot!

🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 203 views
  • 0 likes
  • 2 in conversation