topic Re: First and last observations in SAS Programming

First and last observations

RandyStan — Wed, 05 Oct 2022 17:37:27 GMT

Dear All:

May data set is as follows

Date ID VarA Interval

01JAN 2019 A 22 1

01JAN 2019 A 26 1

01JAN 2019 A 21 1

01JAN 2019 A 24 2

01JAN 2019 A 27 2

01JAN 2019 B 19 1

01JAN 2019 B 23 1

01JAN2019 B 16 7

02JAN 2019 A 95 1

02JAN 2019 A 62 1

02JAN 2019 A 21 1

02JAN 2019 A 43 2

02JAN 2019 A 51 2

02JAN 2019 B 33 1

02JAN 2019 B 45 5

02JAN2019 B 56 5

I want my data to be as follows:

Date ID Interval VarA_FO VarA_LO

01JAN 2019 A 1 22 21

01JAN 2019 A 2 24 27

01JAN 2019 B 1 19 23

01JAN2019 B 7 16 16 /* FO = LO */

02JAN 2019 A 1 95 21

02JAN 2019 A 2 43 51

02JAN 2019 B 1 33 33 /* FO = LO */

02JAN 2019 B 5 45 56

Thanks in advance.

Randy

Re: First and last observations

Reeza — Wed, 05 Oct 2022 18:06:46 GMT

data have;
infile cards;
input Date  : date9.                        ID   $            VarA             Interval;
cards;
01JAN2019             A                 22                   1
01JAN2019             A                 26                 1
01JAN2019             A                 21                 1
01JAN2019             A                24                   2
01JAN2019             A                 27                  2
01JAN2019             B                19                   1
01JAN2019             B                23                   1
01JAN2019             B                 16                   7
02JAN2019             A                 95                 1
02JAN2019             A                 62                 1
02JAN2019             A                 21                 1
02JAN2019             A                 43                 2
02JAN2019             A                 51                 2
02JAN2019             B                33                   1
02JAN2019             B                45                   5
02JAN2019             B                 56                   5
;;;;
run;

proc sort data=have;
by id interval date;
run;

data fo_lo;
set have;
by id interval date;
if first.interval and last.interval then do;
Type = 'FO';
output;
Type = 'LO';
output;
end;
else do;
  if first.interval then type = 'FO';
  if last.interval then type = 'LO';
  output;
end;


run;

proc transpose data=fo_lo out=want;
where not missing(type);
by id interval date;
var vara;
id type;
run;

@RandyStan wrote:

Dear All:

May data set is as follows

Date ID VarA Interval

01JAN 2019 A 22 1

01JAN 2019 A 26 1

01JAN 2019 A 21 1

01JAN 2019 A 24 2

01JAN 2019 A 27 2

01JAN 2019 B 19 1

01JAN 2019 B 23 1

01JAN2019 B 16 7

02JAN 2019 A 95 1

02JAN 2019 A 62 1

02JAN 2019 A 21 1

02JAN 2019 A 43 2

02JAN 2019 A 51 2

02JAN 2019 B 33 1

02JAN 2019 B 45 5

02JAN2019 B 56 5

I want my data to be as follows:

Date ID Interval VarA_FO VarA_LO

01JAN 2019 A 1 22 21

01JAN 2019 A 2 24 27

01JAN 2019 B 1 19 23

01JAN2019 B 7 16 16 /* FO = LO */

02JAN 2019 A 1 95 21

02JAN 2019 A 2 43 51

02JAN 2019 B 1 33 33 /* FO = LO */

02JAN 2019 B 5 45 56

Thanks in advance.

Randy

Re: First and last observations

Tom — Wed, 05 Oct 2022 20:49:57 GMT

Seems simple. You will need to RETAIN the value from the first observation.

data have;
  input date :date. id $ vara interval ;
  format date date9.;
cards;
01JAN2019 A 22 1
01JAN2019 A 26 1
01JAN2019 A 21 1
01JAN2019 A 24 2
01JAN2019 A 27 2
01JAN2019 B 19 1
01JAN2019 B 23 1
01JAN2019 B 16 7
02JAN2019 A 95 1
02JAN2019 A 62 1
02JAN2019 A 21 1
02JAN2019 A 43 2
02JAN2019 A 51 2
02JAN2019 B 33 1
02JAN2019 B 45 5
02JAN2019 B 56 5
;

data want;
  set have;
  by date id interval;
  if first.interval then vara_fo=vara;
  retain vara_fo;
  if last.interval;
  vara_lo=vara;
  drop vara ;
run;

OBS         date    id    interval    vara_fo    vara_lo

 1     01JAN2019    A         1          22         21
 2     01JAN2019    A         2          24         27
 3     01JAN2019    B         1          19         23
 4     01JAN2019    B         7          16         16
 5     02JAN2019    A         1          95         21
 6     02JAN2019    A         2          43         51
 7     02JAN2019    B         1          33         33
 8     02JAN2019    B         5          45         56