topic Re: How do I RETAIN/LAG a String variable by ID? in SAS Programming

How do I RETAIN/LAG a String variable by ID?

Pili1100 — Thu, 18 Nov 2021 19:26:05 GMT

How should I Write the code to get the desired output like WORK.want?

IF FIRST.ID THEN Desired_values = CHANNEL;

ELSE Desired_values = lag(CHANNEL);
BY ID;

DATA WORK.have; 
Infile datalines delimiter=','; 
INPUT ID $	CHANNEL $;
;
LENGTH CHANNEL $50;
CARDS;
123ABC,Service,
123ABC,Online,
123ABC,Retail,
456EFG,Stores,
456EFG,Retail,
789HIJ,Service,
789HIJ,Other,
789HIJ,Retail,
012KLM,Online,
012KLM,Retail,
;
RUN;
DATA work.want;
Infile datalines delimiter=','; 
INPUT ID $	CHANNEL $ Desired_values $;
;
LENGTH CHANNEL Desired_values $50;
CARDS;
123ABC,Service,Service
123ABC,Online,Service
123ABC,Retail,Online
456EFG,Stores,Stores
456EFG,Retail,Stores
789HIJ,Service,Service
789HIJ,Other,Service
789HIJ,Retail,Other
012KLM,Online,Online
012KLM,Retail,Online
;
RUN;

Re: How do I RETAIN/LAG a String variable by ID?

PaigeMiller — Thu, 18 Nov 2021 19:39:02 GMT

DATA WORK.have; 
Infile datalines delimiter=','; 
INPUT ID $	CHANNEL $;
CARDS;
123ABC,Service,
123ABC,Online,
123ABC,Retail,
456EFG,Stores,
456EFG,Retail,
789HIJ,Service,
789HIJ,Other,
789HIJ,Retail,
012KLM,Online,
012KLM,Retail,
;

data want;
    set have;
    by id notsorted;
    prev_channel=lag(channel);
    if first.id then desired_values=channel;
    else desired_values=prev_channel;
    drop prev_channel;
run;

Re: How do I RETAIN/LAG a String variable by ID?

Astounding — Thu, 18 Nov 2021 20:06:27 GMT

Probably simplest to lag and then overwrite for the first one:

data want;
   set have;
   by id notsorted;
   desired_values = lag(channel);
   if first.id then desired_values = channel;
run;

Re: How do I RETAIN/LAG a String variable by ID?

Kurt_Bremser — Fri, 19 Nov 2021 07:53:26 GMT

NEVER call the LAG function in a conditional branch, unless you perfectly know what you are doing.

The LAG function creates a FIFO queue which is filled only when the function is called (not automatically with every data step iteration!).

One method to make sure that a new value is entered into the queue is the use of IFC or IFN:

data want;
set have;
by id;
desired_values = ifc(first.id,channel,lag(channel));
run;

because these functions evaluate ALL arguments before returning a value.

Re: How do I RETAIN/LAG a String variable by ID?

Pili1100 — Tue, 23 Nov 2021 10:00:20 GMT

Thx for solution 🙂

Re: How do I RETAIN/LAG a String variable by ID?

Pili1100 — Tue, 23 Nov 2021 10:01:23 GMT

Thx for solution and for the heads up when using LAG 🙂