Count record by two variables

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 93
Accepted Solution

Count record by two variables

Hi everyone, I am working on order data. In my data as below, each row represents a package for an order, and each order has at least one package. When shipped=1, it means that the package was shipped. paid=1 means that the package was paid. I need to identify 1st payment for 1st shipment of an order. Some special examples are highlighted in my data. For example, for order #2, the 5th package (pack_num=5) counts; for #4, the 3rd package (pack_num=3) counts, and for #7, the 2nd visit  (pack_num=3) counts, and for 17, the 3rd package counts.

pack_tot is the total number of packages per order.

Thanks a lot!

orderidpack_numpack_totshippedpaid
11200
12211
21501
22500
23500
24500
25511
31911
32900
33900
34911
35901
36901
37901
38900
39901
41400
42401
43411
44411
512300
522300
532300
542300
552311
562311
572300
582311
592300
5102300
5112311
5122300
5132311
5142300
5152311
5162311
5172311
5182311
5192311
5202311
5212311
5222300
5232311
61400
62411
63400
64400
71601
72611
73600
74600
75600
76600
81400
82400
83400
84411
91201
92211
101111
111111
121400
122400
123400
124411
131111
141300
142300
143311
151400
152411
153400
154400
161400
162400
163400
164411
171501
172510
173501
174510
175501
181211
182201
191111
201111

Accepted Solutions
Solution
‎10-20-2014 08:34 PM
Respected Advisor
Posts: 4,609

Re: Count record by two variables

I think you need something like:

data want;

do until(last.orderId);

    set have; by orderId;

    hasShipped = hasShipped or shipped;

    if hasShipped and not isPaid then

        if paid then do;

            output;

            isPaid = 1;

            end;

    end;

drop hasShipped isPaid;

run;

proc print data=want noobs; run;

PG

PG

View solution in original post


All Replies
Solution
‎10-20-2014 08:34 PM
Respected Advisor
Posts: 4,609

Re: Count record by two variables

I think you need something like:

data want;

do until(last.orderId);

    set have; by orderId;

    hasShipped = hasShipped or shipped;

    if hasShipped and not isPaid then

        if paid then do;

            output;

            isPaid = 1;

            end;

    end;

drop hasShipped isPaid;

run;

proc print data=want noobs; run;

PG

PG
Frequent Contributor
Posts: 93

Re: Count record by two variables

Thank you so much, PG. You are AWESOME!

Frequent Contributor
Posts: 93

Re: Count record by two variables

Hi PG, actually is it possible to make flag variables for the first Shipped and the first Paid after the first Shipped in the same dataset?

Thanks again!

Respected Advisor
Posts: 4,609

Re: Count record by two variables

Please give an example of the result you want. - PG

PG
Frequent Contributor
Posts: 93

Re: Count record by two variables

Thanks so much, PG. My target data is listed as below.

So the target variables are Firstship and Firstpay_afterfirstship. Basically the same question as previously, but I need the binary variable Firstship to indicate a row/record has the first shipping. Firstpay_afterfirstship is generated to flag that a row/record has the first payment after first shipping.

orderidpack_numpack_totshippedpaidFirstshipFirstpay_afterfirstship
1120000
1221111
2150100
2250000
2350000
2450000
2551111
3191111
3290000
3390000
3491100
3590100
3690100
3790100
3890000
3990100
4140000
4240100
4341111
4441100
51230000
52230000
53230000
54230000
55231111
56231100
57230000
58231100
59230000
510230000
511231100
512230000
513231100
514230000
515231100
516231100
517231100
518231100
519231100
520231100
521231100
522230000
523231100
6140000
6241111
6340000
6440000
7160100
7261111
7360000
7460000
7560000
7660000
8140000
8240000
8340000
8441111
9120100
9221111
10111111
11111111
12140000
12240000
12340000
12441111
13111111
14130000
14230000
14331111
15140000
15241111
15340000
15440000
16140000
16240000
16340000
16441111
17150100
17251010
17350101
17451000
17550100
18121111
18220100
19111111
20111111
Respected Advisor
Posts: 4,609

Re: Count record by two variables

That's a bit different:

data want;

retain hasShipped isPaid;

set have; by orderId;

if first.orderId then call missing(hasShipped, isPaid); /* missing = FALSE */

firstShip = shipped and not hasShipped;

hasShipped = hasShipped or firstShip;

Firstpay_afterfirstship = hasShipped and paid and not isPaid;

isPaid = isPaid or Firstpay_afterfirstship;

drop hasShipped isPaid;

run;

proc print data=want noobs; run;

PG

PG
Frequent Contributor
Posts: 93

Re: Count record by two variables

Thanks so much again, PG!

It's a nice learning process to me.

Lizi

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 424 views
  • 3 likes
  • 2 in conversation