BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Pili1100
Obsidian | Level 7

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26
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;
--
Paige Miller

View solution in original post

5 REPLIES 5
PaigeMiller
Diamond | Level 26
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;
--
Paige Miller
Astounding
PROC Star

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;
Pili1100
Obsidian | Level 7

Thx for solution 🙂

Kurt_Bremser
Super User

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.

Pili1100
Obsidian | Level 7

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

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1974 views
  • 0 likes
  • 4 in conversation