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 !
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
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
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.
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;
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;
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.