CODEWITHSUNDEEP
sasretain
stunt
stunt

Reputation: 49

creating same values within a group using retain statement in SAS

I am trying to create a variable z that will take the same value within a group based on values of two variables X and Y of the first observation within a group. There are 4 possible values of Z a group can take based on the X and Y values of the first observation in the group.

Z=1 (if X=1 & Y=1), 
Z=2 (if X=2 & Y=1),
Z=3 (if X=1 & Y=2), and 
Z=4 (if X=2 & Y=2).

This is what I have and what I want.

X has two values, 1 or 2, within a group; while Y can take 1, 2 ,3. 
Y is sorted in ascending order 
if the first (or all group observations) take a value of 3, the resulting Z 
value should be set to missing

This is what I have:

Obs    Group          X            Y
1      10600          1            1
2      10600          1            2
3      10600          1            3
4      10800          2            1
5      10800          2            3
6      10900          1            2
7      10900          1            3
8      11100          2            2
9      11100          2            2
10     11100          2            3
11     11100          2            2
12     11200          2            3
13     11300          2            1
14     11300          2            2
15     11300          1            3
16     11300          1            3
17     11300          1            3
18     11300          1            3

And here is what I want: 

Obs    Group          X            Y         Z
1      10600          1            1         1
2      10600          1            2         1
3      10600          1            3         1
4      10800          2            1         2
5      10800          2            3         2
6      10900          1            2         3
7      10900          1            3         3
8      11100          2            2         4
9      11100          2            2         4
10     11100          2            3         4
11     11100          2            2         4
12     11200          2            3         .
13     11300          2            1         2
14     11300          2            2         2
15     11300          1            3         .
16     11300          1            3         .
17     11300          1            3         .
18     11300          1            3         .

Thank you!

Upvotes: 1

Views: 3104

Answers (2)

Richard
Richard

Reputation: 27498

You are correct that a retained variable will carry a value into forward iterations of the data step. Nominally, a simple data step with a single set statement an iteration will correspond to a row in the data set.

Your retained variable is to be assigned at the start of a group, so you will need a by statement, which in turn makes an automatic flag variable first.<by-group-var> available.

data have; input
Group  X Y; datalines;
10600  1 1
10600  1 2
10600  1 3
10800  2 1
10800  2 3
10900  1 2
10900  1 3
11100  2 2
11100  2 2
11100  2 3
11100  2 2
11200  2 3
11300  2 1
11300  2 2
11300  1 3
11300  1 3
11300  1 3
11300  1 3
run;

The last set of rows with group=11300 have x=2 followed by x=1. Your narrative

within a group

conveys an idea but is not explicitly precise. The actual grouping (based on the shown want) appears to be a combination of group and x. Thus, you will need a

by group x notsorted;

statement. The notsorted will cause the data step setup the first. and last. based on continguity of the values instead of the explicit ordering of values.

data want;
  set have;
  by group x nostsorted;
  retain z;
  if first.x then do;  * detect first row in combinations "group/x";
    select;
      when (X=1 & Y=1) Z=1;  * apply logic for retained value;
      when (X=2 & Y=1) Z=2;
      when (X=1 & Y=2) Z=3;
      when (X=2 & Y=2) Z=4;
      otherwise Z=.;
    end;
  end;
  logic_tracker_first_x = first.x;
run;

ods listing; options nocenter;
proc print data=want;
run;

The output window shows

                               logic_tracker_
Obs    Group    X    Y    z      first_x

  1    10600    1    1    1           1
  2    10600    1    2    1           0
  3    10600    1    3    1           0
  4    10800    2    1    2           1
  5    10800    2    3    2           0
  6    10900    1    2    3           1
  7    10900    1    3    3           0
  8    11100    2    2    4           1
  9    11100    2    2    4           0
 10    11100    2    3    4           0
 11    11100    2    2    4           0
 12    11200    2    3    .           1
 13    11300    2    1    2           1
 14    11300    2    2    2           0
 15    11300    1    3    .           1
 16    11300    1    3    .           0
 17    11300    1    3    .           0
 18    11300    1    3    .           0

Upvotes: 1

Rhythm
Rhythm

Reputation: 682

please try using the following solution , I have used simpler approach by keeping only First Z variable per Group and then did a left join with same dataset to keep the First z variable across remaining observations for same group-

data test;
input group 5. x 1. y 1.;
if x=1 and y=1 then z=1;
else if x=2 and y=1 then z=2;
else if x=1 and y=2 then z=3;
else if x=2 and y=2 then z=4;
datalines;
1060011
1060012
1060013
1080021
1080023
1090012
1090013
1110022
1110022
1110023
1110022
1120023
1130021
1130022 
1130013
1130013
1130013
1130013
;
run;

data test1;
 set test;
 keep group x z;
run;

proc sort data=test1; by group x; run;

data keep_first;
 set test1;
  by group x;
  if first.group or first.x;
run;

proc sql;
create table final
as
select a.group, a.x, a.y, b.z
from test a
left join keep_first b
on a.group=b.group
and a.x=b.x
order by a.group, a.y, a.x;
quit;

Upvotes: 1

Related Questions