Help using Base SAS procedures

counting number of missing from back date

Reply
Super Contributor
Posts: 395

counting number of missing from back date

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

Super User
Posts: 17,734

Re: counting number of missing from back date

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?

Super Contributor
Posts: 395

Re: counting number of missing from back date

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

Super User
Posts: 17,734

Re: counting number of missing from back date

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

Super Contributor
Posts: 1,636

Re: counting number of missing from back date

Is this helpful?

data have;

input scen $ date1-date3;

cards;

scen1 2 3 4

scen2 2 . 5

scen3 . . 3

;

data want(keep=scen _Smiley Happy;

  set have;

  _3month=n(of date1-date3);

run;

proc print;run;

                              Obs    scen     _3month

                               1     scen1       3

                               2     scen2       2

                               3     scen3       1

Linlin

Super Contributor
Posts: 395

Re: counting number of missing from back date

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

Super Contributor
Posts: 1,636

Re: counting number of missing from back date

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

Super Contributor
Posts: 395

Re: counting number of missing from back date

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

Super Contributor
Posts: 1,636

Re: counting number of missing from back date

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

Super Contributor
Posts: 395

Re: counting number of missing from back date

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.

Super User
Posts: 9,662

Re: counting number of missing from back date

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

Respected Advisor
Posts: 3,124

Re: counting number of missing from back date

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

Ask a Question
Discussion stats
  • 11 replies
  • 236 views
  • 0 likes
  • 5 in conversation