Linlin, Matt already answered your question. However, I, too, often don't know what the abbreviations mean that some people use. I always look them up at: http://www.abbreviations.com/FWIW
Thank you Art and Matt!
One thing that makes SAS so powerful a programming language is that you do not need to think of your operations in terms of iterations. You can think of them as processes to apply to the dataset as a whole.
For this data you need to look through the 2010 data to find the largest value less than the current year's value.
So you can split out the 2010 records as another dataset and look through that as you process your original data.
proc sort data=input out=year2010(rename=(povratio=x perpovratio=y) drop=inty) ;
where inty=2010;
by descending povratio;
run;
data want ;
set input ;
if inty=2010 then do ;
x=povratio; y=perpovratio ;
output;
end;
else do p=1 to nobs until(x<povratio);
set year2010 point=p nobs=nobs ;
if x < povratio then output;
end;
if p > nobs then do ;
x=.; y=.; output;
end;
run;
proc compare data=want compare=output;
run;
Comparison of WORK.WANT with WORK.OUTPUT
(Method=EXACT)
Data Set Summary
Dataset Created Modified NVar NObs
WORK.WANT 09DEC11:01:33:05 09DEC11:01:33:05 5 16
WORK.OUTPUT 09DEC11:01:33:05 09DEC11:01:33:05 5 16
Variables Summary
Number of Variables in Common: 5.
Observation Summary
Observation Base Compare
First Obs 1 1
Last Obs 16 16
Number of Observations in Common: 16.
Total Number of Observations Read from WORK.WANT: 16.
Total Number of Observations Read from WORK.OUTPUT: 16.
Number of Observations with Some Compared Variables Unequal: 0.
Number of Observations with All Compared Variables Equal: 16.
NOTE: No unequal values were found. All values compared are exactly equal.
Hi Tom,
I didn't know we can use where statement in proc sort. Is this only avaliable in newer SAS versions?
Thanks!
If by new you mean some time in the last 10 to 15 years then it is new.
data foo;
input inty povratio perpovratio;
datalines;
1990 4 10
1991 5 20
1992 2 30
1993 5 40
1994 6 50
1994 8 55
1995 3 60
1996 8 70
1997 7 80
1998 5 90
1999 8 95
2010 4.9 110
2010 1.6 120
2010 2.1 130
2010 7.9 140
2010 4.6 150
;
run;
data bar;
length inty povratio perpovratio x y 8;
if _n_=1 then do;
declare hash f(dataset:'foo(where=(inty=2010) rename=(povratio=x perpovratio=y))',ordered:'a');
f.definekey('x');
f.definedata('x','y');
f.definedone();
declare hiter i('f');
end;
set foo;
if inty=2010 then do;
x=povratio; y=perpovratio;
output;
end;
else do;
i.first();
do until(i.next()>0 or x>povratio); end;
i.prev();
output;
end;
run;
inty povratio perpovratio x y
1990 4.0 10 2.1 130
1991 5.0 20 4.9 110
1992 2.0 30 1.6 120
1993 5.0 40 4.9 110
1994 6.0 50 4.9 110
1994 8.0 55 7.9 140
1995 3.0 60 2.1 130
1996 8.0 70 7.9 140
1997 7.0 80 4.9 110
1998 5.0 90 4.9 110
1999 8.0 95 7.9 140
2010 4.9 110 4.9 110
2010 1.6 120 1.6 120
2010 2.1 130 2.1 130
2010 7.9 140 7.9 140
2010 4.6 150 4.6 150
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.