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
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?
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
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...
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
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
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
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
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
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.
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.