topic Re: Count number of changes in variables over time in SAS Enterprise Guide

Count number of changes in variables over time

KKS2 — Wed, 31 Jan 2018 10:40:11 GMT

Hi All

This is my first time posting so please excuse me if i have missed anything.

I use enterprise guide and i have a data set that looks like this below. With thousands of IDs. I want to add an extra variable(ENTRIES) that sums the number of times the ID entered over time. the dates go back many year and will continue going forwards.

Any help will be appreciated.

Thanks

KKS2

ID	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Entries
1111	.	.	1	1	1	.	1	(entered in Sept17)
1112	1	.	1	1	.	1	3	(entered in July17 then Sept17 and then dec17)
1113	.	.	.	.	1	1	1	(entered in Nov17)
1114	.	1	1	1	1	1	1	(entered in Aug17)
1115	1	.	1	1	1	1	2
1116	1	.	1	.	.	.	2
1117	1	.	.	.	1	.	2
1118	1		.	.	1	.	2
1119	.	1	.	1	1	.	2
1120	1	.	1	1	.	1	3
1121	.	1	1	.	.	.	1

Re: Count number of changes in variables over time

RW9 — Wed, 31 Jan 2018 11:09:56 GMT

To note, post test data in the form of a datastep, follow this post if unsure:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Use the code window which is the {i} above the post are to post these things.

There are two main ways to approach this. The first is to stick with the model you have now for your data - to do this you would then employ arrays. Array all the dated variables, then loop over that array turning on a flag when 1 is encountered and turning it off and adding 1 to entries when . or last, maybe something like:

data want;
  set have;
  array dates jul17--dec17;
  onflag=0;
  entries=0;
  do i=1 to dim(dates);
    if dates{i}=1 and onflag=0 then onflag=1;
    if dates{i}=. and onflag=1 then do;
      onflag=0;
      entries=sum(entries,1);
    end;
    if i=dim(dates) and onflag=1 then sum(entries,1);
  end;
run;

This will work, but as always, thinking in Excel terms, and having data transposed across the page is not by any means the best way to work with the data (or in fact from a resource or storage point of view). Which leads onto the second method, normalising your data, so you have a layout like:

ID DATE FLAG

1111 Jul17 .

1111 Aug17 .

...

This type of data is far simpler an easier to program with and can still create your Excel transposed output with a simple call to proc transpose. You can also effectively shrink your data down using this method, in your given case Jul17 has an entry for 1111, even though it doesn't capture anything in the above, we can filter down and only keep the information we actually need:

ID DATE FLAG

1111 Sep17 1

1111 Oct17 1

...

In fact we can then go further than this and by a datastep with a by group select just the start and stop dates eliminating all the data in between to end with something like:
ID START END

1111 Sep17 Nov17

1112 Jul17 Jul17

1112 Sep17 Oct17

...

Thus drastically shrinking the data, and making things like counting number of entries, days within entries and such like far simpler.

Re: Count number of changes in variables over time

Gangi — Wed, 31 Jan 2018 11:12:14 GMT

you can use sum statement,

example as below,

data t1;
input id jul_17 Aug_17 sep_17 oct_17 nov_17 dec_17;
datalines;
1111 . . 1 1 1 1
1112 . . . . . 1
1113 1 1 . 1 . .
1114 . . . . 1 1
1115 1 . . . 1 .
1116 1 . . . 1 .
;
run;

data t2;
set t1;
ENTRIES= sum(of jul_17--Dec_17);
run;

accept it if it works..

Re: Count number of changes in variables over time

KKS2 — Wed, 31 Jan 2018 11:21:02 GMT

Thanks RW9. I will try this code.

i do have the data in this format:

ID DATE FLAG

1111 Sep17 1

1111 Oct17 1

so if there is a code for this format, that will also be helpful.

Re: Count number of changes in variables over time

RW9 — Wed, 31 Jan 2018 11:38:34 GMT

Simplest if you have that is:

data want;
  set have;
  by id;
  if first.id or last.id then output;
  else if date ne intnx('month',lag(date),1) then output;
run;

That should then give you any non-consecutive dates (note I haven't tested as not typing in the test data), i.e. distinct periods.

Re: Count number of changes in variables over time

Ksharp — Wed, 31 Jan 2018 12:59:41 GMT


data t1;
input id jul_17 Aug_17 sep_17 oct_17 nov_17 dec_17;
datalines;
1111 . . 1 1 1 1
1112 . . . . . 1
1113 1 1 . 1 . .
1114 . . . . 1 1
1115 1 . . . 1 .
1116 1 . . . 1 .
;
run;

data want;
 set t1;
 n=countw(cat(of jul_17--dec_17),'.');
run;

Re: Count number of changes in variables over time

KKS2 — Wed, 31 Jan 2018 14:36:11 GMT

Thank you. this also works!