counting number of missing from back date

[podarum]

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

[Reeza]

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?

[podarum]

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         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         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

[Reeza]

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...

[Linlin]

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

[podarum]

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

[Linlin]

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

[podarum]

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

[Linlin]

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

[podarum]

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.

[Ksharp]

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

[Haikuo]

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 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