subsetting via do loops & if then statements in macro

[sonicpoem]

I would like to subset a dataset if the row matches my condition.

There are n conditions (combination of variable a and b) that resolved correctly in prior steps.  Now, I'm trying to subset the test1 to test2 dataset using the loops (from beginning of the row to final row, j=1 to &final) and if then conditions to create an indicator variable "match".  However, all the "match" variable rows turned out to be zero (0) in test2 dataset, (I do know there are several test1 dataset rows that has the combination I'm seeking to subset.)  Help!

%macro matchdisplay;

    data test2;

    set test1;

        %do j=1 %to &final;

            %do i=1 %to &n;

                %if a=&&var&i and b=&&var&i %then %do; match=1; %end;

                %else %do; match=0; %end;

            %end;

         where match=1;

        %end;

run;

%mend matchdisplay;

%matchdisplay

[ballardw]

Did you start with something that worked without any macro variables? You should post that and we might be able to help turn that into a working macro.

This code has several issues that appear to be mixing datastep variables and macro logic which generally are not going to work. If you are attempting to compare the value of a dataset variable A or B then this fails. You would not want a macro %if in that case.

%if a=&&var&i and b=&&var&i %then %do; match=1; %end;

                %else %do; match=0; %end;

And it may help to provide an example data set, preferably as data step code, and what the results should be.

[sonicpoem]

Lost connection to the network just now.  Will provide the data Monday.  Thanks for the prompt response.

[sonicpoem]

Here's an example scenario, data and sample codes (YTD2015):

BRAND	ITEM	QT
A	apple	1
B	orange	1
C	melon	1
D	apple	1
A	apple	1
A	apple	1
B	orange	1
B	orange	1
D	melon	2
D	apple	2
C	orange	2
B	melon	2
A	apple	2
A	orange	2
C	melon	3
C	apple	3
C	orange	3
D	melon	3
A	apple	3
B	melon	3

*Viewed 2015 Quarter 1 (Q1) item list to identify the group and item combination that has the threshold of 3 or more ;

data sample1;

    set sample;

    if QT=1;

run;

proc sort data=sample1;by group item;run;

data sample2;

    set sample1;

    by group item;

    if first.item then count=1;

    else count+1;

run;

data sample3;

    set sample2;

    if count ge 3;

run;

*created macro variables for hit combination;

    data _null_;

        set sample3 end=eof;

        call symputx('Groupname'||left(_n_), brand, 'G');

        call symputx('Product'||left(_n_), item, 'G');

        if eof then call symputx('numrows',_n_, 'G');

    run;

%macro popular;

    %do i=1 %to &numrows;

        %put Signal #&i: &&Groupname&i and &&Product&i;

     %end;

%mend;

%popular;

*Gone back to the original YTD2015 list of brands and items to find YTD count of hit combination identified in Q1;

data _null_;

    set sample end=eod;

    if eod then call symputx('all',_n_,'G');

run;

%macro ytd;

    data sample4;

    set sample;

        %do j=1 %to &all;

            %do i=1 %to &numrows;

                %if group=&&Groupname&i and item=&&Product&i %then %do;

                    match=1;

                %end;

                %else %do;

                    match=0;

                %end;

            %end;

        %end;

        if match=1;

run;

%mend ytd;

%ytd  

/*Log showed   "NOTE: The data set WORK.SAMPLE4 has 0 observations and 4 variables."  

[ballardw]

Minor bit:

data sample1;
    set sample;
    if QT=1;
run;

proc sort data=sample1;by group item;run;
data sample2;
    set sample1;
    by group item;
    if first.item then count=1;
    else count+1;
run;

data sample3;
    set sample2;
    if count ge 3;
run;

Likely can be replaced with:

proc sort data=sample;by group item; run;

data sample2;

    set sample1 (where=(qt=1));

    by group item;

    if first.item then count=1;

    else count+1;

     if last.item and count ge 3 then output;

run;

I can't tell exactly what you mean by

YTD count of hit combination identified in Q1;

But it sure sounds like something that could generate lots of the combinations from Proc Summary with the right class variables.

[sonicpoem]

Basically, in QT1, you can see that two group and item combinations (A & apple and B & orange) meet my criteria (occurring 3 or more times in the quarter).  Once that is identified, I am looking at the rest of the data (YTD) to subset all the rows that met these two combinations (hopefully tagged with  Match=1).  In the past, I manually subset the YTD by hard-coding the program once the combination(s) of interest was(were) identified.  I was wondering if there's a way to automate this process using macro.

[ndp · Accepted solution]

This is will flag match=1 all combinations of group and item found in sample3:

%macro ytd;
proc sql noprint;
select distinct("if group="||quote(strip(group))||" and item="||quote(strip(item))||" then match=1;") into: _condlst separated by " "
from sample3;
quit;

data sample4;
set sample;
match=0;
&_condlst.;
run;
%mend;

%ytd;

[sonicpoem]

First of all, I apologize for mixing up variable names: group (description and code) and brand (data). They are interchangeable in this scenario.

Second of all, thanks everyone for helping me to troubleshoot this issue.

Finally, thanks to ndp.  Your suggestion worked!    (I marked it "Correct Answer.")

[Reeza]

What is the expected outcome based on that input?

[sonicpoem]

The subser of YTD dataset containing all the rows with the combination of group and item identified from the 1st QT.

[RickAster]

Based on the macro logic I don't think this would ever pick out an observation. It all hinges on the macro variable references &Groupname20 and &Product20, which occur in the last iteration of the loop where &i is &numrows. Based on the sample data, these macro variables would never get the values group and item, respectively, so you would always generate the statement match = 0;.

I am not clear on what the actual objective is but if it involves counting occurrences of observations that is hard to do with macro programming and would more easily be done with proc step programming or data step programming.

[RickAster]

This is starting to sound like an inner join. That is, if I understand correctly, the result would be:

proc sql;

create table test2 as

    select a.*

    from sample a inner join sample2 b

    on a.group = b.group and a.item = b.item;

quit;

Maybe I'm missing something but I think this is the general idea.

[Tom]

If we look at your %IF statement we can see why this approach can never work.

%if a=&&var&i and b=&&var&i %then %do; match=1; %end;

%else %do; match=0; %end;

So if I = 1 then &&VAR&I is the value of macro variable VAR1.

Now your IF condition is testing if this macro variable contains a string of characters that is equal to the character string lowercase a AND the character string lowercase b.  It can never be equal to both of those things at the same time.  So you macro code will just generate the data step code:

match=0;