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 🙂

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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