topic Re: assign value to macro in SAS Programming

assign value to macro

jojozheng — Tue, 23 Oct 2018 19:22:06 GMT

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 !

Re: assign value to macro

novinosrin — Tue, 23 Oct 2018 19:29:48 GMT

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

Re: assign value to macro

jojozheng — Tue, 23 Oct 2018 19:53:53 GMT

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.

Re: assign value to macro

novinosrin — Tue, 23 Oct 2018 20:01:19 GMT

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

Re: assign value to macro

jojozheng — Tue, 23 Oct 2018 20:14:54 GMT

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.

Re: assign value to macro

novinosrin — Tue, 23 Oct 2018 21:24:38 GMT

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;

Re: assign value to macro

s_lassen — Wed, 24 Oct 2018 11:33:45 GMT

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;

Re: assign value to macro

s_lassen — Fri, 26 Oct 2018 07:49:34 GMT

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