Help with SAS retain code

[maple_tandoori]

I'm having difficulty writing a piece of SAS code and I'm seeking the most effective approach within SAS. I have a variable named 'type' (with values 1, 2, 3, 4) and client IDs. Essentially, I aim to create another variable called 'modified_type,' which follows these rules:

1. If there's a single unique client ID with only 1 in the 'type' variable, then 'modified_type' should be set to 0.
2. However, if there are multiple client IDs with the same number having 1 in the 'type' variable, 'modified_type' should be set to 1.
3. If there are 2 in the 'type' variable, then 'modified_type' should be set to 2... and so on.
   
   

I tried using the retain function so something like this:

retain modified_type 0;
if first.client_id then modified_type = 0;

if type = '1' or type = '1st' then modified_type = 1;
else if type = '2' or = '2nd' then modified_type = 2;

and so on.....
but these does seem to retain 0 in the type and for some reason shows as 1.

Yea im note sure the best way to do this, a newbie at SAS so would even appreciate the point in the right direction:

[Tom]

Code looks fine.  But we will need to see example input data to know if your logic makes sense.  Share a simple input dataset and the expected result for that dataset.

Note the RETAIN statement just means that SAS will not reset the value to missing when it starts a new iteration of the data step.  The 0 in the retain statement means the initial value before the first iteration starts will be 0 instead of missing.

Any variable that is coming in from an existing dataset via a SET/MERGE/UPDATE statement is already being retained, but you normally do not notice because the retained value is replaced when the SET/MERGE/UPDATE statement executes.  So modified_type should NOT already exist in the source dataset.

[maple_tandoori]

Hi Tom,

Thank you so much for your reply. Thank you for explanation on retain, i think it makes sense for my purposes as i want every clientid that appears only once to have new variable (modified_type) = 0. Please allow me show a example of the sample data and my excepted out to show you what i mean, the modified type is the new variable i want to create:

Client_id          type            modified type

1122                      1                             1                        (one here because there is another entry for id 1122)
1122                      1                             1
1523                      1                             0                      (0 here because only 1 entry for id 1523)
2789                      1                            1
2789                     2                            2
2789                      3                           3
8776                     1                             0
9900                   1                             0

[Tom]

So you need to also use BY group processing.

Let's convert your example listing into an actual dataset.

data have;
  input Client_id type;
cards;
1122 1
1122 1
1523 1
2789 1
2789 2
2789 3
8776 1
9900 1
;

Now we can set the new variable to 0 when there is only one observation for the BY group (when it is both the first and the last there is only one) and 1 when there are more than one.

data want;
  set have;
  by client_id;
  if first.client_id then new_var=not last.client_id;
  else new_var+1;
run;

Result

       Client_
Obs       id      type    new_var

 1       1122       1        1
 2       1122       1        2
 3       1523       1        0
 4       2789       1        1
 5       2789       2        2
 6       2789       3        3
 7       8776       1        0
 8       9900       1        0

Note that your example results does not seem to the right result for the second observation for the first client.

[Patrick · Accepted solution]

Based on what I understood and your sample data below should do.

data have;
  infile datalines dsd dlm=',' truncover;
  input (Client_id type modified_type_desired) (:best32.);
  datalines;
1122,1,1,(one here because there is another entry for id 1122)
1122,1,1
1523,1,0,(0 here because only 1 entry for id 1523)
2789,1,1
2789,2,2
2789,3,3
8776,1,0
9900,1,0
;

data want;
  set have;
  by Client_id;
  if first.Client_id and last.Client_id then modified_type_derived=0;
  else modified_type_derived=type;
run;

proc print data=want;
run;

[maple_tandoori]

Thank you Patrick, your way works better than my initial retain method.