topic Re: Retaining values for given IDs in SAS Programming

Retaining values for given IDs

ifb10 — Mon, 02 Jan 2023 12:17:34 GMT

Hello everyone,

I have a large excel sheet with around 100.000 data points of following structure:

ID	Value
1	3
1	3
1	3
2	2
2	3
2	4
2	0
2	0
3	9
3	10
3	10
3	0
3	0
3	0
3	0

I would like to extend the last non 0 value for each ID, to replace the 0s. The final table should look like this:

ID	Value
1	3
1	3
1	3
2	2
2	3
2	4
2	4
2	4
3	9
3	10
3	10
3	10
3	10
3	10
3	10

Does anybody have a suggestions? I have tried with lag function with loops but can not seem to find the right solution.

Thank you for any advice!

Re: Retaining values for given IDs

PaigeMiller — Mon, 02 Jan 2023 12:26:55 GMT

data want;
    set have;
    by id;
    retain newvalue;
    prev_value=lag(value);
    if first.id or value>0 then newvalue=value;
    if value=0 and not first.id and prev_value^=0 then newvalue=prev_value;
    drop prev_value;
run;

Please do us a favor, help us out (which also helps you get quicker and better answers), from now on do not provide example data as screen capture or as file attachments; instead provide data as working SAS data step code.

Re: Retaining values for given IDs

PeterClemmensen — Mon, 02 Jan 2023 13:10:26 GMT

data have;
input ID Value;
datalines;
1  3  
1  3  
1  3  
2  2  
2  3  
2  4  
2  0  
2  0  
3  9  
3  10 
3  10 
3  0  
3  0  
3  0  
3  0  
;

data want;
   set have;
   by ID;
   if Value then _iorc_ = Value;
   if not first.id and Value = 0 then Value = _iorc_;
run;

Result:

Re: Retaining values for given IDs

ifb10 — Mon, 02 Jan 2023 15:26:58 GMT

Thank you this does the trick. I have been trying to overwrite the original value instead of creating a new one. This is a very simple and effective solution!