Proc SQL

[jaiganesh]

Hello Team,

Please find input below.

Product    Location
0025743    475
0025743    109
0R4007    109
0R4007    109

I'm looking for out the the combine (Product & Location Variable) distinct count. Output i'm expecting is :

Product    Location count
0025743    475        1
0025743   109         1
0R4007    109         2 (As i've product and location same)
0R4007    109         2 (As i've product and location same)

Can one please help to get this done with proc sql.

[PeterClemmensen]

I don't follow this logic. Why is count=1 for Product=0025743 and 2 for 0R4007?

[jaiganesh]

Hello,

Count=1 for Product=0025743  because we are looking for the Distinct count of Product and Location Variable. Here Product 0025743  has appeared  twice but the independent Location, Hence Each entry could be calculated as Count 1.

If you follow this logic then, Product 0R4007 with Location 109  has appeared twice in the table, Hence Count is 2.

I'm looking for SQL help on this.

[Aku]

proc sql;
  create table want as
  select Product,
            Location,
            count(location) as count
   from have
   group by Product, Location;
quit;

[Reeza]

Welcome to the forums @Aku 

I believe your answer will only return one of the lines for the last record rather than the two lines?

I think you would need to remerge it here. Oddly, if there were another variable this wouldn't be an issue. 

---

@Aku wrote:

proc sql;
  create table want as
  select Product,
            Location,
            count(location) as count
   from have
   group by Product, Location;
quit;

---

[jaiganesh]

Yes 3rd variable is making problem.

This logic is not sufficient for more then 2 variable.

[dvart1]

Count of the combination of product & location...

[novinosrin]

Hello @jaiganesh  You need another pass I am afraid in SQL

data have;
input Product :$20.    Location;
cards;
0025743    475
0025743    109
0R4007    109
0R4007    109
;

proc sql;
create table want as
select a.*, count
from have a, (select *,count(*) as count from have group by product,location)b
where a.product=b.product and a.location=b.location;
quit;

[novinosrin]

Hi @jaiganesh  And if you really want to remerge, you'd still need an extra-pass like the below to have a junk extra var created but it doesn't look good when you can handle with an in-line view and join your "have" as demonstrated above,

data have;
input Product :$20.    Location;
cards;
0025743    475
0025743    109
0R4007    109
0R4007    109
;

data temp;
set have;
n+1;
run;

proc sql;
create table want(drop=n) as
select *,count(*) as count
from temp
group by product,location;
quit;

[Jagadishkatam]

Alternatively if you would like to try a data step as well

proc sort data=want;
by Product Location;
run;

data want2;
do until(last.Location);
set want;
by Product Location;
retain cnt;
if first.Location then cnt=1;
else cnt+1;
end;
do until(last.Location);
set want;
by Product Location;
output;
end;
run;