topic Re: Differences between quarters in SAS Programming

Differences between quarters

RandyStan — Sun, 02 Sep 2018 02:54:23 GMT

Dear All:
My Data is as follows
Date                               ID           ID_A            Quarter
03JAN2017                      1                  A                   1
04JAN2017                      1                  A                   1
04APR2017                      1                  A                   2
10MAY2017                      1                  A                   2
09AUG2017                      1                  A                   3
16FEB2017                      1                  B                   1
18SEP2018                      1                  B                   7
13JAN2017                      2                  A                   1
15FEB2017                      2                  A                   1
21JUN2017                      2                  A                   2
14MAR2017                      2                  B                   1
21NOV2017                      2                  B                   4

I want to add another variable QBT indicating the quarters between transactions

Date                          ID               ID_A            Quarter          QBT
03JAN2017                      1                 A             1                 1
04JAN2017                      1                 A             1                 1
04APR2017                      1                 A             2                 1
10MAY2017                      1                 A             2                 1
09AUG2017                      1                 A             3               -50000 /* Just a flag */
16FEB2017                      1                 B             1                 6
18SEP2018                      1                 B             7                -50000
13JAN2017                      2                 A             1                 1
15FEB2017                      2                 A             1                 1
21JUN2017                      2                 A             2                -50000
14MAR2017                      2                 B             1                 3
21NOV2017                      2                 B             4               -50000

The rules are as follows:

For ID = 1 and ID_A = A  there are transactions in 3 Quarters -- 1, 2 and 3

so QBT is the difference between Quarter  = 2 and Quarter  = 1

So for the first two lines QBT = 1; then for the next two lines QBT = 1 also (3 -2).  The last row of ID and ID_A is = -50000

The same logic applies to ID = 1 and ID_A = B

Thanx in Advance

Re: Differences between quarters

Ksharp — Sun, 02 Sep 2018 10:58:28 GMT

data have;
input Date :$20.      ID           ID_A    $    Quarter;
cards;
03JAN2017                      1                  A                   1
04JAN2017                      1                  A                   1
04APR2017                      1                  A                   2
10MAY2017                      1                  A                   2
09AUG2017                      1                  A                   3
16FEB2017                      1                  B                   1
18SEP2018                      1                  B                   7
13JAN2017                      2                  A                   1
15FEB2017                      2                  A                   1
21JUN2017                      2                  A                   2
14MAR2017                      2                  B                   1
21NOV2017                      2                  B                   4
;
run;
proc sort data=have(keep=id id_a quarter) out=temp nodupkey;
by id id_a quarter;
run;
data diff;
 merge temp temp(firstobs=2 rename=(id=_id id_a=_id_a quarter=_quarter));
 if id=_id and id_a=_id_a then diff=_quarter-quarter;
  else diff=-50000;
 drop _:;
run;
data want;
 merge have diff;
 by id id_a quarter;
run;

Re: Differences between quarters

RandyStan — Sun, 02 Sep 2018 21:15:54 GMT

Dear Mr. KSharp:

Your solution does not result in the output that I need. Your solution of finding the difference gives the following

DATE                          ID                 ID_A               QUARTER         QBT
03JAN2017                      1                  A                   1              0     
04JAN2017                      1                  A                   1              1
04APR2017                      1                  A                   2              0
10MAY2017                      1                  A                   2              1
09AUG2017                      1                  A                   3             -50000

What I need is

DATE                          ID                ID_A              QUARTER     QBT
03JAN2017                      1                  A                   1        1
04JAN2017                      1                  A                   1        1
04APR2017                      1                  A                   2        1
10MAY2017                      1                  A                   2        1
09AUG2017                      1                  A                   3       -50000

Please help

Thanx

Re: Differences between quarters

Kurt_Bremser — Mon, 03 Sep 2018 13:22:49 GMT

I shamelessly stole the example data from @Ksharp and expanded the basic idea (as you need a "reverse" difference):

data have;
input Date :$20.      ID           ID_A    $    Quarter;
cards;
03JAN2017                      1                  A                   1
04JAN2017                      1                  A                   1
04APR2017                      1                  A                   2
10MAY2017                      1                  A                   2
09AUG2017                      1                  A                   3
16FEB2017                      1                  B                   1
18SEP2018                      1                  B                   7
13JAN2017                      2                  A                   1
15FEB2017                      2                  A                   1
21JUN2017                      2                  A                   2
14MAR2017                      2                  B                   1
21NOV2017                      2                  B                   4
;
run;

proc sort
  data=have(keep=id id_a quarter)
  out=temp
  nodupkey
;
by id id_a descending quarter;
run;

data diff;
set temp;
by id id_a;
qbt = lag(quarter) - quarter;
if first.id_a then qbt = -50000;
run;

proc sort data=diff;
by id id_a quarter;
run;

data want;
merge
  have
  diff
;
by id id_a quarter;
run;

proc print data=want noobs;
run;

Result:

  Date       ID    ID_A    Quarter       qbt

03JAN2017     1     A         1            1
04JAN2017     1     A         1            1
04APR2017     1     A         2            1
10MAY2017     1     A         2            1
09AUG2017     1     A         3       -50000
16FEB2017     1     B         1            6
18SEP2018     1     B         7       -50000
13JAN2017     2     A         1            1
15FEB2017     2     A         1            1
21JUN2017     2     A         2       -50000
14MAR2017     2     B         1            3
21NOV2017     2     B         4       -50000

which matches your originally wanted result:

Date                          ID               ID_A            Quarter          QBT
03JAN2017                      1                 A             1                 1
04JAN2017                      1                 A             1                 1
04APR2017                      1                 A             2                 1
10MAY2017                      1                 A             2                 1
09AUG2017                      1                 A             3               -50000 /* Just a flag */
16FEB2017                      1                 B             1                 6
18SEP2018                      1                 B             7                -50000
13JAN2017                      2                 A             1                 1
15FEB2017                      2                 A             1                 1
21JUN2017                      2                 A             2                -50000
14MAR2017                      2                 B             1                 3
21NOV2017                      2                 B             4               -50000

Re: Differences between quarters

Ksharp — Mon, 03 Sep 2018 13:05:19 GMT

That is really weird. Here is my output .

Re: Differences between quarters

Kurt_Bremser — Mon, 03 Sep 2018 13:21:46 GMT

@RandyStan wrote:

Dear Mr. KSharp:

Your solution does not result in the output that I need. Your solution of finding the difference gives the following
DATE ID ID_A QUARTER QBT
03JAN2017 1 A 1 0 04JAN2017 1 A 1 1 04APR2017 1 A 2 0 10MAY2017 1 A 2 1 09AUG2017 1 A 3 -50000

What I need is

DATE ID ID_A QUARTER QBT
03JAN2017 1 A 1 1 04JAN2017 1 A 1 1 04APR2017 1 A 2 1 10MAY2017 1 A 2 1 09AUG2017 1 A 3 -50000
Please help

Thanx

R

You must have run something different, as @Ksharp's code



data have;
input Date :$20.      ID           ID_A    $    Quarter;
cards;
03JAN2017                      1                  A                   1
04JAN2017                      1                  A                   1
04APR2017                      1                  A                   2
10MAY2017                      1                  A                   2
09AUG2017                      1                  A                   3
16FEB2017                      1                  B                   1
18SEP2018                      1                  B                   7
13JAN2017                      2                  A                   1
15FEB2017                      2                  A                   1
21JUN2017                      2                  A                   2
14MAR2017                      2                  B                   1
21NOV2017                      2                  B                   4
;
run;
proc sort data=have(keep=id id_a quarter) out=temp nodupkey;
by id id_a quarter;
run;
data diff;
 merge temp temp(firstobs=2 rename=(id=_id id_a=_id_a quarter=_quarter));
 if id=_id and id_a=_id_a then diff=_quarter-quarter;
  else diff=-50000;
 drop _:;
run;
data want;
 merge have diff;
 by id id_a quarter;
run;

proc print data=want noobs;
run;

gives me the same result as mine:

  Date       ID    ID_A    Quarter      diff

03JAN2017     1     A         1            1
04JAN2017     1     A         1            1
04APR2017     1     A         2            1
10MAY2017     1     A         2            1
09AUG2017     1     A         3       -50000
16FEB2017     1     B         1            6
18SEP2018     1     B         7       -50000
13JAN2017     2     A         1            1
15FEB2017     2     A         1            1
21JUN2017     2     A         2       -50000
14MAR2017     2     B         1            3
21NOV2017     2     B         4       -50000

which also matches your originally wanted result.