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

I have a dataset in the form of 

id total_days
A60
A90
A60
B40
B20
B90
B70

 

I want to add two variables whose values would represent the start and stop days of each row. For instance, for id A, it would be

id total_daysstartstop
A60160
A9061150
A60151210

 

I used the following code and was fairly confident that it would give me what I was looking for. However, the start and stop variables were populated only in the first row of each id and were missing in other rows. I am unable to understand why the codes did not work. 

data cum_days;
set num_days;
by id;
if first.id then do;
start=1;
stop=total_days;
end;
else do;
start=lag(stop) + 1;
stop= start + total_days -1;
end;
run;

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
data have;
input id $	total_days;
datalines;
A	60
A	90
A	60
B	40
B	20
B	90
B	70
;
data want;
 set have;
 by id;
 if first.id then stop=0;
 stop+total_days;
 start=lag(stop)+1;
 if first.id then start=1;
run;

View solution in original post

6 REPLIES 6
novinosrin
Tourmaline | Level 20
data have;
input id $	total_days;
datalines;
A	60
A	90
A	60
B	40
B	20
B	90
B	70
;

data want;
set have;
by id;
retain start stop;
if first.id then do; start=1;stop=total_days;end;
else do;start=stop+1;stop+total_days;end;
run;
ballardw
Super User

The issue is the LAG function queue behavior.

 

You might consider RETAINing the value of Stop

data want;
   set have;
   by id;
   Retain stop;
   if first.id then do;
      start=1;
      stop=total_days;
   end;
   else do;
      start=stop + 1;
      stop= start + total_days -1;
   end;
run;
PGStats
Opal | Level 21

No lag, no retain required with a DO UNTIL() loop:

 

data have;
input id $	total_days;
datalines;
A	60
A	90
A	60
B	40
B	20
B	90
B	70
;

data want;
stop = 0;
do until (last.id);
    set have; by id;
    start = stop + 1;
    stop + total_days;
    output;
    end;
run;

proc print data=want noobs; var id total_days start stop; run;
PG
Ksharp
Super User
data have;
input id $	total_days;
datalines;
A	60
A	90
A	60
B	40
B	20
B	90
B	70
;
data want;
 set have;
 by id;
 if first.id then stop=0;
 stop+total_days;
 start=lag(stop)+1;
 if first.id then start=1;
run;
Mahip
Obsidian | Level 7

Thank you very much! I would also like to understand how your set of codes were processed differently than mine. It would be of much help if you could walk me through your codes, so that I would be able to solve similar issues on my own in future. Also, what was the issue with my codes?

Ksharp
Super User

 

/*******stop is cumulative sum of total_days, I think you understand it.***********/
if
first.id then stop=0; /*since cum is happened in a group,so set it be zero at the first obs.*/ stop+total_days;

/*it is easy to understand. get lag value of stop and + 1 as start variable*/ start=lag(stop)+1;

/*when reaching the first obs of a group, start would be the last stop of the previous group*/
/*Therefore, set it be 1 manually*/ if first.id then start=1;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 6 replies
  • 1067 views
  • 1 like
  • 5 in conversation