lundi 29 juillet 2019

SAS count unique observation by group

I am looking to figure out how many customers get their product from a certain store. The problem each prod_id can have up to 12 weeks of data for each customer. I am trying to use the data count function as i have heard proc SQL's count function is not accurate. with large datasets. I have tried a multitude of codes, some add up all of the obersvations for each customer while others like the one below remove all but the last observation.

proc sort data= have; BY Prod_ID cust; run;
Data want;
Set have;
by Prod_Id cust;
if (last.Prod_Id and last.cust);
    count= +1;
run;

data have

prod_id cust week  store
1       A    7/29  ABC
1       A    8/5   ABC
1       A    8/12  ABC
1       A    8/19  ABC
1       B    7/29  ABC
1       B    8/5   ABC
1       B    8/12  ABC
1       B    8/19  ABC
1       B    8/26  ABC
1       C    7/29  XYZ
1       C    8/5   XYZ
1       F    7/29  XYZ
1       F    8/5   XYZ   
2       A    7/29  ABC
2       A    8/5   ABC
2       A    8/12  ABC
2       A    8/19  EFG
2       C    7/29  EFG
2       C    8/5   EFG
2       C    8/12  EFG
2       C    8/19  EFG
2       C    8/26  EFG

data want

prod_id store count
1       ABC    2
1       XYZ    2
2       ABC    1
2       EFG    2

Aucun commentaire:

Enregistrer un commentaire