BookmarkSubscribeRSS Feed
PetePatel
Quartz | Level 8

 

Spoiler
 

Hi SAS Community.

 

 

I have a dataset with three variables and I am trying to create the fourth one 'need'

 

'Need' is months since Flag is last>0 resetting to 1 once flag>0. I hope I explained this correctly, but essentially you can see how the variable should be created from the datastep below.

 

The dataset is already sorted by ID and Date. How can I code this efficiently?

 

Datastep is below:

 

data have;
input ID Mon $ Flag Need;
datalines;
1 Jan-06 0 0
1 Feb-06 0 0
1 Mar-06 0 0
1 Apr-06 0 0
1 May-06 0 0
1 Jun-06 0 0
1 Jul-06 0.7 1
1 Aug-06 0 2
1 Sep-06 1 1
1 Oct-06 1.3 1
1 Nov-06 0 2
1 Dec-06 0 3
2 Feb-14 0 0
2 Mar-14 0 0
2 Apr-14 0 0
2 May-14 0 0
2 Jun-14 0.6 1
2 Jul-14 0 2
2 Aug-14 0 3
2 Sep-14 0 4
2 Oct-14 0 5
2 Nov-14 2 1
3 Aug-18 0 0
3 Sep-18 0 0
3 Oct-18 0 0
3 Nov-18 0 0
3 Dec-18 0 0
;
run;

1 REPLY 1
Kurt_Bremser
Super User

Easily done with a retained variable that is set or incremented on conditions:

data have;
input ID Mon $ Flag Need;
datalines;
1 Jan-06 0 0
1 Feb-06 0 0
1 Mar-06 0 0
1 Apr-06 0 0
1 May-06 0 0
1 Jun-06 0 0
1 Jul-06 0.7 1
1 Aug-06 0 2
1 Sep-06 1 1
1 Oct-06 1.3 1
1 Nov-06 0 2
1 Dec-06 0 3
2 Feb-14 0 0
2 Mar-14 0 0
2 Apr-14 0 0
2 May-14 0 0
2 Jun-14 0.6 1
2 Jul-14 0 2
2 Aug-14 0 3
2 Sep-14 0 4
2 Oct-14 0 5
2 Nov-14 2 1
3 Aug-18 0 0
3 Sep-18 0 0
3 Oct-18 0 0
3 Nov-18 0 0
3 Dec-18 0 0
;

data want;
set have (rename=(need=want_need));
by id;
if first.id then need = 0;
if flag ne 0 then need = 1;
else if need ne 0 then need + 1;
run;

The retain is done automatically because of the use of the SUM Statement (need + 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 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
  • 1 reply
  • 378 views
  • 0 likes
  • 2 in conversation