topic Re: find most consecutive months with missing price in SAS Procedures

find most consecutive months with missing price

podarum — Tue, 21 Aug 2012 20:36:07 GMT

Hi,

If I have a dataset by City, and date and price. How can I get a count of consecutive months that the price is missing by City.

Have

City Month Price

Tor 01 345

Tor 02 .

Tor 03 .

Tor 04 367

Tor 05 678

Mon 01 .

Mon 02 235

Mon 03 .

Mon 04 .

Win 01 234

Win 02 456

Win 03 .

Win 04 .

Win 05 .

Want

City ConsMonth

Tor 2

Mon 1

Win 5

Thanks

Re: find most consecutive months with missing price

Linlin — Tue, 21 Aug 2012 20:44:48 GMT

How did you come up the number in data set want?

Re: find most consecutive months with missing price

podarum — Wed, 22 Aug 2012 01:40:11 GMT

Well, first of all it would help if I gave out the right want results.:smileyblush: Sorry about that..

It should be

Want

City ConsMonth

Tor 2

Mon 2

Win 3

How many consecutive blank months (shown by prices)... Again, sorry about that.

Re: find most consecutive months with missing price

MikeZdeb — Wed, 22 Aug 2012 02:01:48 GMT

hi ... here's one way ...

data x;

input city :$3. month price @@;

datalines;

Tor 01 345 Tor 02 . Tor 03 . Tor 04 367 Tor 05 678

Mon 01 . Mon 02 235 Mon 03 . Mon 04 . Win 01 234

Win 02 456 Win 03 . Win 04 . Win 05 .

;

proc summary data=x missing nway;

by city price notsorted;

output out=stats;

run;

proc sql;

select city, max(_freq_) as consmonths from stats group by city;

quit;

city consmonths

Mon 2

Tor 2

Win 3

Re: find most consecutive months with missing price

Ksharp — Wed, 22 Aug 2012 02:27:59 GMT

How about:

data have;
input City $    Month   Price ;
cards;
Tor       01          345
Tor       02           .
Tor       03           .
Tor       04          367
Tor       05          678
Mon     01           .
Mon     02         235
Mon     03          .
Mon     04          .
Win      01        234
Win      02       456
Win      03         .
Win      04         .
Win      05         .
;
run;
data have;
 set have;
 if city ne lag(city) or not missing(price) then n=0;
 if missing(price) then n+1;
run;

proc sql;
create table want(where=(n ne 0)) as
 select city,max(n) as n
  from have 
   group by city ;
quit;

Ksharp

Message was edited by: xia keshan

Re: find most consecutive months with missing price

Ksharp — Wed, 22 Aug 2012 02:34:27 GMT

Mike,

Your code is not right ,try the data below.

Tor	01	345
Tor	02	345
Tor	03	.
Tor	04	367
Tor	05	678
Mon	01	.
Mon	02	235
Mon	03	.
Mon	04	.

Re: find most consecutive months with missing price

MikeZdeb — Wed, 22 Aug 2012 02:48:15 GMT

hi ... you are correct, I forgot the WHERE in SUMMARY ...

proc summary data=x missing nway;

by city price notsorted;

output out=stats (where=(missing(price)));

run;

proc sql;

select city, max(_freq_) as consmonths from stats group by city;

quit;

Re: find most consecutive months with missing price

Ksharp — Wed, 22 Aug 2012 04:38:18 GMT

Mike,

First of all, I like your code.

But After the discussion with mkeintz at another session , I found an interesting problem, if missing value is a special like .A .Z .Result is totally different.

Ksharp

Re: find most consecutive months with missing price

data_null__ — Wed, 22 Aug 2012 12:26:12 GMT

You can fix that with a FORMAT.

proc format;

value miss(default=8) ._-.Z = .;

run;

proc summary data=x missing nway;

by city price notsorted;

output out=stats(where=(missing(price)));

format price miss.;

run;

Re: find most consecutive months with missing price

MikeZdeb — Wed, 22 Aug 2012 12:31:37 GMT

hi ... correct again, so group all possible missing values with a format ......

data x;

input city :$3. month price @@;

datalines;

Tor 01 345 Tor 02 .H Tor 03 . Tor 04 367 Tor 05 678

Mon 01 . Mon 02 235 Mon 03 . Mon 04 .

Win 01 234 Win 02 456 Win 03 . Win 04 .L Win 05 .L

;

proc format;

value price ._ - .Z = ' ';

run;

proc summary data=x missing nway;

by city price notsorted;

output out=stats (where=(missing(price)));

format price price.;

run;

proc sql;

select city, max(_freq_) as consmonths from stats group by city;

quit;

city consmonths

Mon 2

Tor 2

Win 3

Re: find most consecutive months with missing price

mkeintz — Wed, 22 Aug 2012 12:45:23 GMT

This is a case where the nested DOW loop approach, in combination with the NOTSORTED by option, is a compact solution:

data want (keep=city nmonths);

do until (last.city);

do n=1 by 1 until (last.price);

set have;

by city price notsorted;

end;

nmonths=max(nmonths,ifn(missing(price),n,0));

end;

run;;

I understood this problem to be finding the maximum number of consecutive months with a missing pri9ce. This program assumes that the data are sorted by city and month.

But see data _null_'s followup below to treat the entire range of missing values (._, .A, .B ... .Z, .) as a single BY level for price.

Message was edited by: Mark Keintz

Re: find most consecutive months with missing price

data_null__ — Wed, 22 Aug 2012 14:13:42 GMT

Add the GROUPFORMAT option to the BY statement and apply format to group all missing values together in the case of special missing.

data want (keep=city nmonths);

do until (last.city);

do n=1 by 1 until (last.price);

set x;

by city price notsorted groupformat;

end;

nmonths=max(nmonths,ifn(missing(price),n,0));

format price miss.;

end;

run;

Re: find most consecutive months with missing price

podarum — Wed, 22 Aug 2012 14:14:01 GMT

Thanks all.. I like Mikeintz's method because it does not count other missing values that belong to the same city but consecutively numbered lower than the larger consecutive series.. for example if there are 5 missing months and then 6 priced months and then again 4 missing months. then it counts only 5 and not 5+4 = 9 ..

Re: find most consecutive months with missing price

MikeZdeb — Wed, 22 Aug 2012 14:27:48 GMT

hi .. yes, that solution to your problem is very nice

but, in case you did not notice, the SUMMARY+SQL solution also does this

"for example if there are 5 missing months and then 6 priced months and then again 4 missing months. then it counts only 5 and not 5+4 = 9"

Re: find most consecutive months with missing price

Linlin — Wed, 22 Aug 2012 14:34:01 GMT

Ksharp's code also gave you 5, not 5+4=9.