assign value to macro

[jojozheng]

Hi everyone,

I have a dataset A like:

month  flag    

sep       A       

sep        B

sep        C

sep        D

oct         A

oct         B

oct         C

nov        A

nov        B

i want to find unmatch rows for two consecutive months, eg, difference between month sep and oct,  flag c and d are in month oct;

for month oct and nov, flag c is not nov. 

able z: for example.(table z will change, so last obs of n can be any number)

n  month

1  sep

2  oct

3  nov

I assign all n and month in :

proc sql noprint;

select month,n into: month1:-,:n1-

macro n is generate from another data step, which is 

data x;
set z(keep=n) end=eof;
if eof then output; run; ---------- I just want last obs from column n. 

and then I want to create a do loop, 

do n=1 to &eof.   eg, if last obs of n=3, then &eof should be 3. 

basically idea I use except: this is a wrong program, just want to explain my idea.

%let n=&eof;

%let i=%eval(&n+1);
%let dates=&dates;

proc sql;create table lost&n as

select * from table A

where month=&&month&n and flag not in 

(select flag from table A where month=&&month&i); --so this lost

I want to create a macro can automatically track last obs of n and put n into proc sql process to generate unmatch flag value between consecutive month. 

so output should include:

table lost1 (contain unmatching row between sep and oct)

table lost2 (contain unmatch rows between otc and nov)

aww...idk if i clarify my question clearly...but any help will be appreciated! Thank you !

[novinosrin]

Before taking a look at the bold macro parts,

To your question i want to find unmatch rows for two consecutive months

sep       A       

sep        B

sep        C

sep        D

oct         A

oct         B

oct         C

To my eyes, 

 Unmatched row i s

Sep D

Can you clarify your requirement clearly plz

Should the logic check

1. sep-oct 

then 2 oct-nov

and so on?

Do you just have 2 vars/columns in dataset or there are many others too

[jojozheng]

yes. your logic is right:
1. sep-oct
then 2 oct-nov
and so on

I have only these two var in table but dataset is huge.

[novinosrin]

No big deal about the size of the long dataset. OK, Before I attempt to code, please provide or confirm a clear sample of HAVE and WANT. I pointed out there is only one non matches sept-oct and your conclusion flag c and d has confused me.  It would also help us going back and forth. Kindly review and confirm.

if my understanding is correct, you don;t need a macro however Hash would make it easy, which would be my approach. 

PS I am not clear with table z. My understanding so far is comparison of by sequential groups sets of 2

[jojozheng]

month  flag    

sep       A       

sep        B

sep        C

sep        D

oct         A

oct         B

oct         C

nov        A

nov        B

between sep and oct, only D is different;

column n in table z is what I want to create to call value.

data zz;
set z(keep=n) end=eof;
if eof then output;
run;

data _null_;
set zz;
call symput('eof',n);run;

data do;
do c=1 to &eof;
n=c;call symput('n',n);-------want to assign every n with a macro 
output;
end;
run;

%let n=&n;
%let i=%eval(&n+1); ---so here, when I run code, n can return me obs contain month sep,  i can return me row of month oct.

so these can be return like

&&month&n=sep

&&month&1=oct 

so I will not manually to assign macro like

%let n=&n1;
%let i=%eval(&n+1);

then return me n=1,i=2

i don't want to manully assign 

%let n=&n2;
%let i=%eval(&n+1);  then return me n=2,i=3. 

i want n assign value automatically like a do loop, then generates me all the reports. 

[novinosrin]

Hi @jojozheng See if this is what you are after or something close to what you are after. If yes, we can do some cosmetics after.

data have;
input (month  flag) ($);   
cards;
sep       A       
sep        B
sep        C
sep        D
oct         A
oct         B
oct         C
nov        A
nov        B
;

data _null_;
if _n_=1 then do;
 dcl hash H () ;
   h.definekey  ("flag") ;
   h.definedata ("month","flag") ;
   h.definedone () ;
end;
k=_n_;
do until(last.month);
	set have;
	by month notsorted;
	if k>1 then do;
	rc=h.check();
	if rc=0 then rc=h.remove();
	else rc=h.add();
	if last.month then do; 
		h.output(dataset:cats('LOST',k));
		h.clear();
	end;
end;
end;
do until(last.month);
	set have;
	by month notsorted;
	rc=h.add();
end;
run;

[s_lassen]

You say your table is huge. If that is because you have many flags and not many months, then something like this may work better than the hash solution:

data diff;
  merge
    have(where=(month='sep') in=in1)
    have(where=(month='oct') in=in2)
    have(where=(month='nov') in=in3)
    ;
  by flag;
  if in1 and not in2 then do;
    month='oct';
    output;
    end;
  if in2 and not in3 then do;
    month='nov';
    output;
    end;
run;

If you do not know the months beforehand, you can generate the code using a datastep:

filename tempsas temp;
filename tempsas2 temp;
data _null_;
  do until(last.month);
    set have;
    by month notsorted;
    end; 
  file tempsas;
  put "have(where=(month='" month "') in=in" _N_ ')';
  file tempsas2;
  if _N_>1 then do;
    prev=_N_-1;
    put 
      'if in' prev 'and not in' _N_ 'then do;'/
      "  month='" month "';" / 
      "  output;" /
      "  end;";
    end;
run;

data diff;
  merge
    %include tempsas/source2;
	;
  by flag;
  %include tempsas2/source2;
run;

[s_lassen]

@jojozheng: You gave my suggestion a like. Does that mean that it works for you? In that case, you should mark it as a solution.