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

Hi all,

 

I have a large dataset with ID, month and flag (1 or 0) as shown below.

 

I am trying to create a count by ID (variable '1_to_0'), so that for each month I can see if the flag has moved from 1 (previous month) to 0 (current month).

 

Then I need to sum all instances for that ID and create a separate variable ('1_to_0_sum').

 

What is the most efficient way of writing this code?

 

Thanks in advance.

 

IDMonthflag1_to_01_to_0_sum
1Mar-120 2
1Apr-120 2
1May-121 2
1Jun-121 2
1Jul-12012
1Aug-120 2
1Sep-121 2
1Oct-12012
2Apr-121 3
2May-12013
2Jun-120 3
2Jul-121 3
2Aug-12013
2Sep-120 3
2Oct-121 3
2Nov-121 3
2Dec-12013
2Jan-130 3
2Feb-131 3
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

Try this:

data want;
prev_flag = 0;
sum_one_to_zero = 0;
do until (last.id);
  set have;
  by id;
  if prev_flag = 1 and flag = 0 then sum_one_to_zero + 1;
  prev_flag = flag;
end;
prev_flag = 0;
do until (last.id);
  set have;
  by id;
  if prev_flag = 1 and flag = 0
  then one_to_zero = 1;
  else one_to_zero = .;
  output;
  prev_flag = flag;
end;
drop prev_flag;
run;

Untested, for lack of usable example data (data step with datalines).

View solution in original post

2 REPLIES 2
Kurt_Bremser
Super User

Try this:

data want;
prev_flag = 0;
sum_one_to_zero = 0;
do until (last.id);
  set have;
  by id;
  if prev_flag = 1 and flag = 0 then sum_one_to_zero + 1;
  prev_flag = flag;
end;
prev_flag = 0;
do until (last.id);
  set have;
  by id;
  if prev_flag = 1 and flag = 0
  then one_to_zero = 1;
  else one_to_zero = .;
  output;
  prev_flag = flag;
end;
drop prev_flag;
run;

Untested, for lack of usable example data (data step with datalines).

mkeintz
PROC Star

This is a good task to wax a little didactic about using the queue-based nature of the lag function:

 

data want;
  set have (in=firstpass)
      have (in=secondpass);
  by id;

  if firstpass then sum_one_to_zero + (lag(flag)=1 and flag=0);
  if first.id then sum_one_to_zero=0;

  if secondpass;

  if lag(flag)=1 and flag=0 then one_to_zero=1;
  if lag(id)^=id then one_to_zero=.;
run;

The statement:

  if firstpass then sum_one_to_zero + (lag(flag)=1 and flag=0);

compares the current flag to the preceding flag, building a total of transitions from 1 to 0. Because the lag function is in the then clause, it is applied only for firstpass cases - secondpass cases never impact the queue underlying the lag function.

 

 

To avoid results from the preceding id contaminating the current id, the sum is reset to zero at the start of each ID.

  if first.id then sum_one_to_zero=0;

 

The third use of the lag function is more subtle.  The statement

  if lag(id)^=id then one_to_zero=.;

appear to test whether the record-in-hand is the start of an id.  So why not just use

  if first.id then one_to_zero=.;  /*Do not use this for secondpass*/

Because this part of the program only deals with second_pass observations, while the first.id condition only exists for firstpass observations.  So you basically have to realize that this part of the program is only processing groups of secondpass observations.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

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
  • 2 replies
  • 714 views
  • 2 likes
  • 3 in conversation