Hi All,
Sample data below
Ref | InvoiceNumber | InvoiceType | Reason |
123 | A1 | NAA | Income |
123 | A2 | NAA | Income |
123 | A3 | DPP | Income |
123 | A4 | PPA | Care |
I would like to do a unique count combing Ref and InvoiceType and a unique count combing Ref and Reason. Output should be as below.
Ref | InvoiceNumber | InvoiceType | Reason | UnqiueRef | UniqueReason | UniqueInvoiceType |
123 | A1 | NAA | Income | 1 | 1 | 1 |
123 | A2 | NAA | Income | |||
123 | A3 | DPP | Income | 1 | ||
123 | A4 | PPA | Care | 1 | 1 |
I had thought of concatenating Ref and InvoiceType (new field UniqueInvoiceType) and also Ref and Reason (new field UniqueReason) and doing an if first on both thos fields.
Is this the best/most efficient way of achiveing this?
Cheers
Haydn
I love this question. What if there are more than one Ref ? data have; input Ref $ 1-3 InvoiceNumber $ 5-6 InvoiceType $ 8-10 Reason $ 12-18; datalines; 123 A1 NAA Income 123 A2 NAA Income 123 A3 DPP Income 123 A4 PPA Care 223 A1 NAA Income 223 A2 NAA Income 223 A3 DPP Income 223 A4 PPA Care ; data want; if _n_=1 then do; if 0 then set have; declare hash h1(dataset:'have'); h1.definekey('ref'); h1.definedone(); declare hash h2(dataset:'have'); h2.definekey('ref','InvoiceType'); h2.definedone(); declare hash h3(dataset:'have'); h3.definekey('ref','Reason'); h3.definedone(); end; set have; if h1.check()=0 then do;UnqiueRef=1;h1.remove();end; if h2.check()=0 then do;UniqueReason=1;h2.remove();end; if h3.check()=0 then do;UniqueInvoiceType=1;h3.remove();end; run;
I am very sure, there will be much easier and cleaner solution then what I have written. if your combination of ref and invoicenumber are unique then you can do sperately each first.group and then join them back as shown below.
data have;
input Ref $ 1-3 InvoiceNumber $ 5-6 InvoiceType $ 8-10 Reason $ 12-18;
datalines;
123 A1 NAA Income
123 A2 NAA Income
123 A3 DPP Income
123 A4 PPA Care
223 A1 NAA Income
223 A2 NAA Income
223 A3 DPP Income
223 A4 PPA Care
;
proc sort data = have out=have1;
by Ref invoicenumber;
run;
data have1a;
set have1;
by Ref invoicenumber;
if first.ref then UnqiueRef = 1; else UnqiueRef =.;
run;
proc sort data = have out=have2;
by Ref InvoiceType;
run;
data have2a;
set have2;
by Ref InvoiceType;
if first.invoicetype = 1 then UniqueInvoiceType =1 ;
else UniqueInvoiceType =.;
run;
proc sort data = have out=have3;
by Ref reason;
run;
data have3a;
set have3;
by Ref reason;
if first.reason = 1 then Uniquereason =1 ;
else Uniquereason =.;
run;
proc sql;
create table want as
select a.Ref,
a.invoicenumber,
a.invoicetype,
a.reason,
a.UnqiueRef,
b.UniqueInvoiceType,
c.Uniquereason
from have1a a, have2a b, have3a c
where a.Ref = b.ref
and a.ref=c.ref
and a.InvoiceNumber =b.InvoiceNumber
and a.InvoiceNumber =c.InvoiceNumber
order by a.Ref, a.InvoiceNumber;
quit;
I love this question. What if there are more than one Ref ? data have; input Ref $ 1-3 InvoiceNumber $ 5-6 InvoiceType $ 8-10 Reason $ 12-18; datalines; 123 A1 NAA Income 123 A2 NAA Income 123 A3 DPP Income 123 A4 PPA Care 223 A1 NAA Income 223 A2 NAA Income 223 A3 DPP Income 223 A4 PPA Care ; data want; if _n_=1 then do; if 0 then set have; declare hash h1(dataset:'have'); h1.definekey('ref'); h1.definedone(); declare hash h2(dataset:'have'); h2.definekey('ref','InvoiceType'); h2.definedone(); declare hash h3(dataset:'have'); h3.definekey('ref','Reason'); h3.definedone(); end; set have; if h1.check()=0 then do;UnqiueRef=1;h1.remove();end; if h2.check()=0 then do;UniqueReason=1;h2.remove();end; if h3.check()=0 then do;UniqueInvoiceType=1;h3.remove();end; run;
Thanks for your help on this.
There will definitely be more than 1 ref in the dataset (it has 2 million rows). I'm running this now and will post back here on how it went.
Cheers
Haydn
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.