topic Re: Proc SQL in New SAS User

Proc SQL

jaiganesh — Thu, 12 Sep 2019 13:38:54 GMT

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.

Re: Proc SQL

PeterClemmensen — Thu, 12 Sep 2019 13:44:26 GMT

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

Re: Proc SQL

jaiganesh — Thu, 12 Sep 2019 13:51:18 GMT

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.

Re: Proc SQL

Aku — Thu, 12 Sep 2019 13:54:16 GMT

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

Re: Proc SQL

Reeza — Thu, 12 Sep 2019 14:00:19 GMT

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;

Re: Proc SQL

jaiganesh — Thu, 12 Sep 2019 14:01:55 GMT

Yes 3rd variable is making problem.

This logic is not sufficient for more then 2 variable.

Re: Proc SQL

novinosrin — Thu, 12 Sep 2019 14:06:02 GMT

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;

Re: Proc SQL

novinosrin — Thu, 12 Sep 2019 14:30:22 GMT

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;

Re: Proc SQL

Jagadishkatam — Thu, 12 Sep 2019 14:44:47 GMT

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;

Re: Proc SQL

dvart1 — Fri, 13 Sep 2019 10:41:44 GMT

Count of the combination of product & location...