topic Re: counting number of missing from back date in SAS Procedures

counting number of missing from back date

podarum — Tue, 17 Apr 2012 20:27:12 GMT

Hi, could someone help please in constructing a way I can count from the back of the date series and see how many fields have non-missing information within the last 3 months, 6-months and 12-months. For example (showing only 3mths):

If I have these 4 scenarios:

Oct'11 Nov'11 Dec'11

Scen1 value value value

Scen2 value . value

Scen3 value . .

Scen4 . . .

Scen5 . value .

Results:

3-mths

Scen1 3

Scen2 2

Scen3 1

Scen4 0

Scen5 1

Thank you

Re: counting number of missing from back date

Reeza — Tue, 17 Apr 2012 20:37:25 GMT

Is that how your data looks?

There's a nmiss function that looks across rows but you'll need some way to identify which columns you'd like to select.

What are your column names like?

Re: counting number of missing from back date

podarum — Tue, 17 Apr 2012 20:43:42 GMT

For example:

Prod Date Price

A Aug'01 43

A Sep'01 34

A Oct'01 .

A Nov'01 .

A Dec'01 .

B Aug'01 45

B Sep'01 23

B Oct'01 34

B Nov'01 9

B Dec'01 .

C Oct'01 .

C Nov'01 .

C Dec'01 .

the results would show something like:

Prod Date Price Missing

A Aug'01 43 3

A Sep'01 34 3

A Oct'01 . 3

A Nov'01 . 3

A Dec'01 . 3

B Aug'01 45 1

B Sep'01 23 1

B Oct'01 34 1

B Nov'01 9 1

B Dec'01 . 1

C Oct'01 . 3

C Nov'01 . 3

C Dec'01 . 3

Re: counting number of missing from back date

Linlin — Tue, 17 Apr 2012 20:49:01 GMT

Is this helpful?

data have;

input scen $ date1-date3;

cards;

scen1 2 3 4

scen2 2 . 5

scen3 . . 3

;

data want(keep=scen _:);

set have;

_3month=n(of date1-date3);

run;

proc print;run;

Obs scen _3month

1 scen1 3

2 scen2 2

3 scen3 1

Linlin

Re: counting number of missing from back date

Reeza — Tue, 17 Apr 2012 20:52:45 GMT

Please post what the results should be following the logic you'd desire.

Ie you have the number missing for each group but that doesn't specify your start date and what you time interval is (3/6/12 month).

It doesn't seem to line up with what I'd expect the results to be and I don't want to guess...

Re: counting number of missing from back date

podarum — Wed, 18 Apr 2012 01:58:48 GMT

Thank you Linlin.. is there a way to code it so it automatically takes the latest 3 months? So if next month Jan'12 gets added, I want to now look at Jan'12, Dec'11 and Nov'11

Re: counting number of missing from back date

Linlin — Wed, 18 Apr 2012 02:40:52 GMT

Yes. Try this one:

data have;

input scen $ date1-date5;

cards;

scen1 2 3 4 2 3

scen2 2 . 5 . 2

scen3 . . . . 1

;

proc contents data=have out=temp(keep=name varnum) ;

proc sort data=temp;

by descending varnum;

run;

proc sql inobs=3 noprint;

select name into :names separated by ' '

from temp;

quit;

/* macro variable &names has the last three variables in the dataset */

data want;

set have;

_3month=n(of &names);

run;

proc print;run;

Obs scen date1 date2 date3 date4 date5 _3month

1 scen1 2 3 4 2 3 3

2 scen2 2 . 5 . 2 2

3 scen3 . . . . 1 1

Linlin

Re: counting number of missing from back date

podarum — Wed, 18 Apr 2012 02:48:15 GMT

Thanks Linlin, but should the output be :

Obs scen date1 date2 date3 date4 date5 _3month

1 scen1 2 3 4 2 3 3

2 scen2 2 . 5 . 2 3.5

3 scen3 . . . . 1 1

Re: counting number of missing from back date

Linlin — Wed, 18 Apr 2012 02:53:04 GMT

what to you want? I thought you want the number of no missing values.

I copied your example below:

Oct'11 Nov'11 Dec'11

Scen1 value value value

Scen2 value . value

Scen3 value . .

Scen4 . . .

Scen5 . value .

Results:

3-mths

Scen1 3

Scen2 2

Scen3 1

Scen4 0

Scen5 1

Re: counting number of missing from back date

podarum — Wed, 18 Apr 2012 03:05:35 GMT

You are totally correct.. I jumped to my next step which for some reason I thought you anticipated, and I got confused. but basically, I was looking to get the 3-month averages for the last 3 months.. So what I'm trying to do is use the 3-month averages, except for the ones that have all 3 months missing I have to input another average from another table I have.. So from the output in Number 7. post, I thought you were also giving me the averages of the last 3 months. Thanks again.

Re: counting number of missing from back date

Ksharp — Wed, 18 Apr 2012 03:16:10 GMT

If I understand what you mean correctly.

data have;
input Prod   $  Date   $     Price ;
cards;
A         Aug'01         43
A         Sep'01         34
A         Oct'01          .
A         Nov'01          .
A         Dec'01         .
B         Aug'01        45
B         Sep'01        23
B         Sep'01        23
B         Oct'01         34
B         Nov'01          9
B         Dec'01         .
C        Oct'01          .
C         Nov'01         .
C         Dec'01         .
;
run;
proc sql;
 create table want as 
  select a.*,missing
   from have as a,( select prod,count(*) as missing 
                     from have
                      where price is missing
                       group by prod ) as b
     where a.prod=b.prod ;
quit;

Ksharp

Re: counting number of missing from back date

Haikuo — Wed, 18 Apr 2012 16:14:56 GMT

It is crucial that what your raw data looks like. I am literally confused upon two versions that you have presented.

One is this;:

Oct'11 Nov'11 Dec'11

Scen1 value value value

Scen2 value . value

Scen3 value . .

Scen4 . . .

Scen5 . value .

Another one is this:

Prod Date Price

A Aug'01 43

A Sep'01 34

A Oct'01 .

A Nov'01 .

A Dec'01 .

B Aug'01 45

B Sep'01 23

B Oct'01 34

B Nov'01 9

B Dec'01 .

C Oct'01 .

C Nov'01 .

C Dec'01 .

And the approaches could be wildly varied against these two.

So Which one is the ONE?

Haikuo