topic Re: Help on Assigning Values Based on Previous and Future Values in SAS Programming

Help on Assigning Values Based on Previous and Future Values

bhavani — Mon, 31 Jan 2011 08:53:50 GMT

Hi
I have this issue where in I am trying to automate a SAS Program.

For every year in process, if a variable does not have value from current year, then i have to check the Previous Year Value and Assign it to the current year; if that value is also blank, then I have to check each subsequent year values and get the first Non missing value. For this I have to define a few macro variables that depends on the number of years in process. For example, if my processing dates are '01JAN2005' till '30JUN2010', the number of Year macro variables will be 5 as

The code construct is as below:
%if &year=2005 %then %do;
%let year1=2006; %let year2=2007; %let year3=2008; %let year4=2009;%let year5=2010;
%end;
%else %if &year=2006 %then %do;
%let year1=2005; %let year2=2007; %let year3=2008; %let year4=2009;%let year5=2010;
%end;
%else %if &year=2007 %then %do;
%let year1=2006; %let year2=2008; %let year3=2009; %let year4=2010;%let year5=2005;
%end;
%else %if &year=2008 %then %do;
%let year1=2007; %let year2=2009; %let year3=2010; %let year4=2006;%let year5=2005;
%end;
%else %if &year=2009 %then %do;
%let year1=2008; %let year2=2010; %let year3=2007; %let year4=2006;%let year5=2005;
%end;
%else %if &year=2010 %then %do;
%let year1=2009; %let year2=2008; %let year3=2007; %let year4=2006;%let year5=2005;
%end;

and so on and so forth.

I had tried using arrays and succeeded a bit, but not in the order that i wanted.
Using this code, I was able to create the number of macro variables that i need. THe problem is with assigning the correct year values as I mentioned above.

Is there a solution.

The code I used:
The GBLDATES is a control dataset that we use to build macro calls and do other processing as well

data gbldates (keep=year st_dt en_dt c);
length year 8 c st_dt en_dt end_1 numyr begyr curyr 8;
format st_dt en_dt end_1 date9.;
retain c 0;
format end_1 date9.;
**Initialize Begin Year;
begyr=2004;

**Initialize Current Year;
curyr=year(today());

**Calculate Total Number Of Year;
numyr=curyr-begyr;

** Establish Extract Start Date;
st_dt=intnx('year',today(), -numyr,'b');

** Establish Extract End Date;
end_1=intnx('quarter',intnx('year',today(),-1,'b'),&qtr.,'e');

** Establish First Quarter End Date from Start Date;
en_dt=intnx('quarter',intnx('year',st_dt,0,'b'),0,'e');

if (en_dt > end_1) then do;
st_dt=st_dt;
en_dt=end_1;
end;

year = year(st_dt);
c+1;
output;

do while(en_dt st_dt=en_dt+1;
en_dt=intnx('quarter',st_dt,0,'e');

if (en_dt>end_1) then do;
st_dt=st_dt;
en_dt=end_1;
end;
year = year(st_dt);
c+1;
output;
end;
run;

filename tmp1 temp;
%let Numyrs=;
%let begsasdt='01JAN2005';
%let endsasdt='30JUN2010'

%macro test;
data _null_;
BegYr=year(&begsasdt.D);
EndYr=year(&endsasdt.D);

numyrs=EndYr-BegYr;

call symput ('Numyrs',put(numyrs,2.));
run;

data sample(Keep=Year year_1-year_%sysfunc(compress(&Numyrs.)));
set gbldates end=eof;
array years{&numyrs.} year_1-year_%sysfunc(compress(&Numyrs.));

do i = 2005 to 2010;
do j = 1 to &numyrs.;
Years(j)=i;
output;
end;
end;
if eof then stop;
run;

proc sort data=sample out=test nodupkey;
by year year_1-year_%sysfunc(compress(&Numyrs.));
run;

data test_1(keep=Year Year_1);
set test(where=(year>2004));
by year year_1 ;

if first.year_1;
if year=year_1 then delete;
run;

%mend;

%test;

proc sort data=test_1;
by year year_1;
run;

data _null_;
Length string $50 ;
Retain String i 0;
set test_1 end=eof;
by Year;
file tmp1;

if first.year then do;
c=1;
buildif='%if &year='||put(year,4.)||' %then %do;';
put buildif;
end;
string='%Let Year'||compress(put(c,2.))||'='||put(year_1,4.)||';';
put @5 string;
c+1;
if last.year then do;
buildifend='%end;';
put buildifend;
end;
run;

THE OUTPUT from the Temporary Files Created:
%if &year=2005 %then %do;
%Let Year1=2006;
%Let Year2=2007;
%Let Year3=2008;
%Let Year4=2009;
%Let Year5=2010;
%end;
%if &year=2006 %then %do;
%Let Year1=2005;
%Let Year2=2007;
%Let Year3=2008;
%Let Year4=2009;
%Let Year5=2010;
%end;
%if &year=2007 %then %do;
%Let Year1=2005;
%Let Year2=2006;
%Let Year3=2008;
%Let Year4=2009;
%Let Year5=2010;
%end;
%if &year=2008 %then %do;
%Let Year1=2005;
%Let Year2=2006;
%Let Year3=2007;
%Let Year4=2009;
%Let Year5=2010;
%end;
%if &year=2009 %then %do;
%Let Year1=2005;
%Let Year2=2006;
%Let Year3=2007;
%Let Year4=2008;
%Let Year5=2010;
%end;
%if &year=2010 %then %do;
%Let Year1=2005;
%Let Year2=2006;
%Let Year3=2007;
%Let Year4=2008;
%Let Year5=2009;
%end;

Re: Help on Assigning Values Based on Previous and Future Values

Peter_C — Mon, 31 Jan 2011 10:31:31 GMT

bhavani

> Hi
> I have this issue where in I am trying to automate a
> SAS Program.
>
> For every year in process, if a variable does not
> have value from current year, then i have to check
> > > > ..... lots more text < < < <
>
> THE OUTPUT from the Temporary Files Created:
> %if &year=2005 %then %do;
> %let Year1=2006;
> %let Year2=2007;
> %let Year3=2008;
> %let Year4=2009;
> %let Year5=2010;
> %end;
> %if &year=2006 %then %do;
> %let Year1=2005;
> %let Year2=2007;
> %let Year3=2008;
> %let Year4=2009;
> %let Year5=2010;
> %end;
> %if &year=2007 %then %do;
> %let Year1=2005;
> %let Year2=2006;
> %let Year3=2008;
> %let Year4=2009;
> %let Year5=2010;
> %end;
> %if &year=2008 %then %do;
> %let Year1=2005;
> %let Year2=2006;
> %let Year3=2007;
> %let Year4=2009;
> %let Year5=2010;
> %end;
> %if &year=2009 %then %do;
> %let Year1=2005;
> %let Year2=2006;
> %let Year3=2007;
> %let Year4=2008;
> %let Year5=2010;
> %end;
> %if &year=2010 %then %do;
> %let Year1=2005;
> %let Year2=2006;
> %let Year3=2007;
> %let Year4=2008;
> %let Year5=2009;
> %end;

bhavani,

I am not sure whether you want to be told
"you are on the right lines,
"I have read and discovered what you want to do,
"and here is where you are almost there
"with just this ????? to add"
or whether you just want to create this kind of collection of macro variable values

I don't see your route to the collection of macro variables and their values as the most effective, obvious or neccessary

Do you want us to create a better solution,
or fix your convoluted program?

We might be even more helpful if we understood the purpose of the macro variables you are trying to generate. Are you able to explain why?

peterC

Re: Help on Assigning Values Based on Previous and Future Values

bhavani — Mon, 31 Jan 2011 11:24:28 GMT

Hi Peter,
The code i wrote is just a start. If there is better solution that you can suggest, it'll be very helpful.

The project is related to Health Care domain.
This program actually fills Holes in the data, that is, populating a particular category of Population which is missing in the source survey, using 3 methods, namely, Year Alternate, Gender Alternate and Region Alternate. And eventually a weight is assigned to an individual which will then be projected to National Level Stats. That is, the total number of patients in a particular category multiplied by the Weight is the National Projection

This process of filling missing values is done on a Yearly process. That is to say, we do quarterly refreshes and each time, the data increases by a quarter, starting from 01jan2005.

Tee weights assignment happens in a very complex manner based on
Age Group(0-4(1), 5-25(2), 26-40(3), 41-65(4), 65+(5)),
Region of Resindence(E(1),W(2),N(3),S(4)),
Gender(Male(1),Female(2)),
Drug Claim Indicator,
Medical Claim Indicator,
Chronic Condition Indicator.
We have a reference source A which will have Weights Assigned for all and every possible group of patients.Consider that there is one group (1,1,1,0,0,0) that has a weight of 0. This missing weight has to be populated from one of the following methods(First Non Blank Value)

1. Look for the same group across other years and get the weight (Year Alternate).
2. If NO weight is available in Year Alternate, then check for (1,1,2,0,0,0) and get the New Weight.(Gender Alternate)
3. If NO weight available in Gender Alt, Then the remaing 3 regions are taken into consideration (1,2,1,0,0,0), (1,3,1,0,0,0) and (1,4,1,0,0,0) and a ratio is calculated for each of the three (some formula). The ratio for the region that is close to 1 is considered and the corresponding weight is assigned to our data.

The reason why I want to create those macro variables is to eliminate human error(we have about 43 SAS Programs that constitue one build). As the data increases by year, so are the number if macro variables(if done manually, there is a chance that one might not update the code with the required number of Year Macro Variables.

My problem does not end there. After the required number of Year macro variables are created, there are the same number of Hash Objects also being created, One hash object per year. These also keep increasing as the number of years of data increases (which is my next peice of automation).

Hope the above explanation is helpful.

Re: Help on Assigning Values Based on Previous and Future Values

bhavani — Mon, 31 Jan 2011 12:29:13 GMT

This is the exact Code construct. This is only a psuedo code. Hope it helps.

%macro hole_fill_com(year);

** NEEDS AUTOMATION ****;
%if &year=2005 %then %do;
%let year1=2006; %let year2=2007; %let year3=2008; %let year4=2009;%let year5=2010;
%end;
%else %if &year=2006 %then %do;
%let year1=2005; %let year2=2007; %let year3=2008; %let year4=2009;%let year5=2010;
%end;
%else %if &year=2007 %then %do;
%let year1=2006; %let year2=2008; %let year3=2009; %let year4=2010;%let year5=2005;
%end;
%else %if &year=2008 %then %do;
%let year1=2007; %let year2=2009; %let year3=2010; %let year4=2006;%let year5=2005;

%end;
%else %if &year=2009 %then %do;
%let year1=2008; %let year2=2010; %let year3=2007; %let year4=2006;%let year5=2005;
%end;
%else %if &year=2010 %then %do;
%let year1=2009; %let year2=2008; %let year3=2007; %let year4=2006;%let year5=2005;
%end;

****************************************************************************;
* YEAR ALTERNATE METHOD *;
****************************************************************************;
data X_filled_yr_alt_&year.
X_nofill_yr_alt_&year.;
length method $10. totals_&year1 totals_&year2 totals_&year3
totals_&year4 totals_&year5 weights_&year1
weights_&year2 weights_&year3 weights_&year4
weights_&year5 8.;

if _n_=1 then do;
** NEEDS AUTOMATION ****;
declare hash y1(dataset:"A_&year1.",hashexp:10);
y1.definekey('proj_key');
y1.definedata("totals_&year1.","weights_&year1.");
y1.definedone();

declare hash y2(dataset:"A_&year2.",hashexp:10);
y2.definekey('Key');
y2.definedata("totals_&year2.","weights_&year2.");
y2.definedone();

declare hash y3(dataset:"A_&year3.",hashexp:10);
y3.definekey('Key');
y3.definedata("totals_&year3.","weights_&year3.");
y3.definedone();

declare hash y4(dataset:"A_&year4.",hashexp:10);
y4.definekey('Key');
y4.definedata("totals_&year4.","weights_&year4.");
y4.definedone();

declare hash y5(dataset:"A_&year5.",hashexp:10);
y5.definekey('Key');
y5.definedata("totals_&year5.","Weights_&year5.");
y5.definedone();

** ADD HASH FOR NEW DATA AS REQUIRED;

call missing(of _all_);
end;
set X_&year._holes;

** NEEDS AUTOMATION ****;
if y1.find()=0 then do;
*** DO SOME PROCESSING;
output X_filled_yr_alt_&year.;
else if y2.find()=0 then do;
*** DO SOME PROCESSING;
output X_filled_yr_alt_&year.;
else if y3.find()=0 then do;
*** DO SOME PROCESSING;
output X_filled_yr_alt_&year.;
else if y4.find()=0 then do;
*** DO SOME PROCESSING;
output X_filled_yr_alt_&year.;
else if y5.find()=0 then do;
*** DO SOME PROCESSING;
output X_filled_yr_alt_&year.;
else output X_nofill_yr_alt_&year.;
end;
end;
end;
end;
end;
run;

****************************************************************************;
* GENDER ALTERNATE METHOD *;
****************************************************************************;
data X_nofill_yr_alt_&year.;
length New_Kwy $6.;
set X_nofill_yr_alt_&year.;
rename totals_&year.=totals_gend_&year.;
if substr(proj_key,3,1)='1'
then proj_key_new=substr(proj_key,1,2)||'2'||substr(proj_key,4,3);
else if substr(proj_key,3,1)='2'
then proj_key_new=substr(proj_key,1,2)||'1'||substr(proj_key,4,3);
run;

data X_filled_gen_alt_&year.
X_nofill_gen_alt_&year.;

length method $10. totals_&year totals_&year1 totals_&year2
totals_&year3 totals_&year4 totals_&year5 weights_&year
weights_&year1 weights_&year2 weights_&year3
weights_&year4 weights_&year5 8.;
if _n_=1 then do;

** NEEDS AUTOMATION ****;
declare hash y1(dataset:"A_&year1.",hashexp:10);
y1.definekey('proj_key');
y1.definedata("totals_&year1.","weights_&year1.");
y1.definedone();

declare hash y2(dataset:"A_&year2.",hashexp:10);
y2.definekey('Key');
y2.definedata("totals_&year2.","weights_&year2.");
y2.definedone();

declare hash y3(dataset:"A_&year3.",hashexp:10);
y3.definekey('Key');
y3.definedata("totals_&year3.","weights_&year3.");
y3.definedone();

declare hash y4(dataset:"A_&year4.",hashexp:10);
y4.definekey('Key');
y4.definedata("totals_&year4.","weights_&year4.");
y4.definedone();

declare hash y5(dataset:"A_&year5.",hashexp:10);
y5.definekey('Key');
y5.definedata("totals_&year5.","Weights_&year5.");
y5.definedone();

** ADD HASH FOR NEW DATA AS REQUIRED;

call missing(of _all_);
end;

set X_nofill_yr_alt_&year.;

** NEEDS AUTOMATION ****;
if y.find()=0 then do;
*** DO SOME PROCESSING;
output X_filled_gen_alt_&year.;
else if y1.find()=0 then do;
*** DO SOME PROCESSING;
output X_filled_gen_alt_&year.;
else if y2.find()=0 then do;
*** DO SOME PROCESSING;
output X_filled_gen_alt_&year.;
else if y3.find()=0 then do;
*** DO SOME PROCESSING;
output X_filled_gen_alt_&year.;
else if y4.find()=0 then do;
*** DO SOME PROCESSING;
output X_filled_gen_alt_&year.;
else if y5.find()=0 then do;
*** DO SOME PROCESSING;
output X_filled_gen_alt_&year.;
else output X_nofill_gen_alt_&year.;
end;
end;
end;
end;
end;
end;
run;

****************************************************************************;
* REGION ALTERNATE METHOD *;
****************************************************************************;

%macro region_alt(chk_yr,indst,outdst,yr);

data X_filled_reg_&yr._alt_&year.
&outdst.;

length proj_key_1 proj_key_2 proj_key_3 $6. totals_1 totals_2
totals_3 weights_1 weights_2 weights_3 8.;
if _n_=1 then do;
declare hash re1(dataset:"A_&chk_yr.",hashexp:10);
re1.definekey('proj_key_1');
re1.definedata('totals_1','weights_1');
re1.definedone();

declare hash re2(dataset:"A_&chk_yr.",hashexp:10);
re2.definekey('proj_key_2');
re2.definedata('totals_2','weights_2');
re2.definedone();

declare hash re3(dataset:"A_&chk_yr.",hashexp:10);
re3.definekey('proj_key_3');
re3.definedata('totals_3','weights_3');
re3.definedone();

call missing(of _all_);
end;
set &indst;
*** DO PROCESSING;
*** DO PROCESSING;
*** DO PROCESSING;
if new_weights_&year.=0 then output &outdst.;
else output X_filled_reg_&yr._alt_&year.;
run;

%mend region_alt;

** NEEDS AUTOMATION ****;
%region_alt(&year , X_nofill_gen_alt_&year., X_nofill_reg_y_alt_&year. ,y );
%region_alt(&year1, X_nofill_reg_y_alt_&year. , X_nofill_reg_y1_alt_&year.,y1);
%region_alt(&year2, X_nofill_reg_y1_alt_&year., X_nofill_reg_y2_alt_&year.,y2);
%region_alt(&year3, X_nofill_reg_y2_alt_&year., X_nofill_reg_y3_alt_&year.,y3);
%region_alt(&year4, X_nofill_reg_y3_alt_&year., X_nofill_reg_y4_alt_&year.,y4);
%region_alt(&year5, X_nofill_reg_y4_alt_&year., X_nofill_reg_y5_alt_&year.,y5);

** NEEDS AUTOMATION ****;
proc append base=X_filled_reg_y_alt_&year. data=X_filled_reg_y1_alt_&year. force;run;
proc append base=X_filled_reg_y_alt_&year. data=X_filled_reg_y2_alt_&year. force;run;
proc append base=X_filled_reg_y_alt_&year. data=X_filled_reg_y3_alt_&year. force;run;
proc append base=X_filled_reg_y_alt_&year. data=X_filled_reg_y4_alt_&year. force;run;
proc append base=X_filled_reg_y_alt_&year. data=X_filled_reg_y5_alt_&year. force;run;

**** DO REMAINING PROCESSING;

%mend hole_fill_com;

****************************************************************************;
%macro nw_mepscom;
%do i = %sysfunc(year(&begsasdt.D)) %to %sysfunc(year(&endsasdt.D));
** Invoke the HOLE_FILL_COM Macro with the required Paramters ;
%hole_fill_com(&i.);
%end;
%mend;

%nw_mepscom;

Re: Help on Assigning Values Based on Previous and Future Values

Peter_C — Mon, 31 Jan 2011 12:36:51 GMT

still considering the model for the imputation stage, but for the final stage here is a data step to generate the YEARn macro variables

%let Yfrom = 2004 ;
%let Yto = 2010 ;
%let YThis = 2004 ;

data _null_ ;
retain adj -1 ;
do n = 1 to (&yTo - &yFrom) ;
year = n + &yFrom + adj ;
if year = &yThis then adj=0;
call symputx( 'year' !! put( n,2. -L), (&yFrom + n + adj) ) ;
end;
stop ;
run;

%put yThis=&Ythis %gen( %eval( &Yto - &yFrom), pattern=%nrstr(year###=&year### ) ) ;

(that gen macro (below) is very handy and GENerates a list which is a numbered list: %gen(6) generates 1 2 3 4 5 6. Here it generates year&n = &&&year&n for each N)
%macro gen(n, pattern=###, from=1)/des='peterC pattern generator' ;
%local i ;
%do i= &from %to &n ;
%sysfunc( tranwrd( %superq(pattern), ###, &i ))
%end ;
/* demo:
%put demo %gen( 7, from=4,pattern= abc###ab### ) ;
*/
%mend gen ;

Re: Help on Assigning Values Based on Previous and Future Values

bhavani — Tue, 01 Feb 2011 02:45:04 GMT

Hi Pete,
The Gen Macro is sure a handy utility. But it does not serve my purpose entirely.
It only generates a list of sequential macro varaibles(which is very good as i can use is at a number of places).

Any work around/suggestions for the issue i stated! Message was edited by: bhavani

Re: Help on Assigning Values Based on Previous and Future Values

chang_y_chung_hotmail_com — Tue, 01 Feb 2011 03:54:34 GMT

bhavani wrote:

> I have this issue where in I am trying to automate a

> SAS Program.

...

> I had tried using arrays and succeeded a bit, but not

> in the order that i wanted.

...

OP's original question was a cute little coding challenge but has been ignored by everyone so far. It can be solved with a simple macro like below.

%macro yearOf(num, year=&gYear, start=&gStart, finish=&gFinish);

%local max left right;

%if &finish <= &start %then %return;

%if &year < &start or &finish < &year %then %return;

%let max = %eval(&finish - &start);

%if &num < 1 or max < &num %then %return;

%if &year = &start %then %do;

%eval(&start + &num)

%end; %else %if &year = &finish %then %do;

%eval(&finish - &num)

%end; %else %do;

%let left = %eval(&year - &start - 1);

%let right = %eval(&max - &left);

%if &num = 1 %then %do;

%eval(&start + &left)

%end; %else %if &num <= &right %then %do;

%eval(&start + &left + &num)

%end; %else %if &right < &num %then %do;

%eval(&finish - &num)

%end;

%mend yearOf;

%*-- check. how to use --*;

%let gStart = 2005;

%let gFinish = 2010;

%let gYear = 2007;

%put year: year1 year2 year3 year4 year5;

%put &gyear: %yearOf(1) %yearOf(2) %yearOf(3) %yearOf(4) %yearOf(5);

%*-- on log

year: year1 year2 year3 year4 year5

2007: 2006 2008 2009 2010 2005

--*;

%*-- another check --*;

%macro check(year, start, finish);

%local n max;

%let max = %eval(&finish - &start);

%*;&year:

%do n = 1 %to &max;

%*; %yearOf(&n, year=&year, start=&start, finish=&finish)

%end;

%mend check;

%macro checkAll(start, finish);

%local year;

%put start=&start finish=&finish;

%do year = &start %to &finish;

%put %check(&year, &start, &finish);

%end;

%mend checkAll;

%checkAll(start=2005, finish=2010)

%*-- on log

start=2005 finish=2010

2005: 2006 2007 2008 2009 2010

2006: 2005 2007 2008 2009 2010

2007: 2006 2008 2009 2010 2005

2008: 2007 2009 2010 2006 2005

2009: 2008 2010 2007 2006 2005

2010: 2009 2008 2007 2006 2005

--*;

%checkAll(start=2005, finish=2011)

%*-- on log

start=2005 finish=2011

2005: 2006 2007 2008 2009 2010 2011

2006: 2005 2007 2008 2009 2010 2011

2007: 2006 2008 2009 2010 2011 2005

2008: 2007 2009 2010 2011 2006 2005

2009: 2008 2010 2011 2007 2006 2005

2010: 2009 2011 2008 2007 2006 2005

2011: 2010 2009 2008 2007 2006 2005

--*;

%checkAll(start=2005, finish=2012)

%*-- on log

start=2005 finish=2012

2005: 2006 2007 2008 2009 2010 2011 2012

2006: 2005 2007 2008 2009 2010 2011 2012

2007: 2006 2008 2009 2010 2011 2012 2005

2008: 2007 2009 2010 2011 2012 2006 2005

2009: 2008 2010 2011 2012 2007 2006 2005

2010: 2009 2011 2012 2008 2007 2006 2005

2011: 2010 2012 2009 2008 2007 2006 2005

2012: 2011 2010 2009 2008 2007 2006 2005

--*;

Re: Help on Assigning Values Based on Previous and Future Values

Peter_C — Tue, 01 Feb 2011 14:07:36 GMT

Hi chang
always like to see colorful solutions 😉

but what macro is supposed to execute at
%yearNum(&n, year=&year, start=&start, finish=&finish)
??

peterC
Oh, and try out my approach.
I thought it was achieving the stated OP objective. As evidence, here is a log (shame my editor does not pass the attributes in "style" like your code)
I've packaged it in a macro to make the demo simpler[pre]177 %macro deliver( YThis = 2005, Yfrom = 2005, Yto = 2010 );
178 %global %gen( %eval( &yTo -&yFrom), pattern= year### ) ;
179 data _null_ ;
180 retain adj -1 ;
181 do n = 1 to (&yTo - &yFrom) ;
182 if ( n + &yFrom + adj ) = &yThis then adj=0;
183 call symputx( 'year' !! put( n,2. -L), (&yFrom + n + adj) ) ;
184 end;
185 stop ;
186 run;
187 %put yThis=&Ythis %gen( %eval( &Yto - &yFrom),
187! pattern=%nrstr(year###=&year### ) ) ;
188 %mend deliver ;
189 %deliver( ythis= 2005)

NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

yThis=2005 year1=2006 year2=2007 year3=2008 year4=2009 year5=2010

190 %deliver( ythis= 2007)

NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

yThis=2007 year1=2005 year2=2006 year3=2008 year4=2009 year5=2010

191 %deliver( ythis= 2010)

NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

yThis=2010 year1=2005 year2=2006 year3=2007 year4=2008 year5=2009

192 %deliver( ythis= 2010, yFrom= 2002, yTo= 2015)

NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

yThis=2010 year1=2002 year2=2003 year3=2004 year4=2005 year5=2006
year6=2007 year7=2008 year8=2009 year9=2011 year10=2012 year11=2013
year12=2014 year13=2015[/pre]
however, I suspect more complexity might be required.

peterC

fixed (hiccup on generating global macro vars)
Message was edited by: Peter.C

Re: Help on Assigning Values Based on Previous and Future Values

bhavani — Tue, 01 Feb 2011 17:01:37 GMT

This solution will definitely work(a little tweaking required).

It is indeed simple.

Thanks Chang.

I do have question though.
Is it possible to have a data step do the same. The reason being that I will then have to construct %LET stmts like in the 1st post. Message was edited by: bhavani

Re: Help on Assigning Values Based on Previous and Future Values

Ksharp — Wed, 23 Feb 2011 02:58:25 GMT

Ksharp Message was edited by: Ksharp

Re: Help on Assigning Values Based on Previous and Future Values

chang_y_chung_hotmail_com — Wed, 23 Feb 2011 15:47:08 GMT

@Ksharp: I am not sure if your code is correct. Below shows the differences when start=2005, finish=2012 and the focus year=2007.

year1 year2 year3 year4 year5 year6 year7

2006 2008 2009 2010 2011 2012 2005 (chang)

2005 2006 2008 2009 2010 2011 2012 (Ksharp)

Re: Help on Assigning Values Based on Previous and Future Values

Ksharp — Fri, 25 Feb 2011 05:25:30 GMT

Sorry.My code is wrong.I did not completely notice what OP want.

Re: Help on Assigning Values Based on Previous and Future Values

Ksharp — Fri, 25 Feb 2011 08:31:11 GMT

OK. Here we go. It is much more complicated than I image.

[pre]
%let start=2005;
%let end=2010;
%let range=%eval(&end - &start +1);
data temp(drop=i);
array year{*} year1-year&range;
do i=1 to dim(year);
year{i}=&start + i;
end;
run;
data year(drop=count i j year&range);
set temp;
year=&start;
output;
array _year{*} year1-year&range;
count=&range;
do i=1 to &range-1;
_year{count}=year1;
count+(-1);
year1=year;
year+1;
if count ge 2 and count le &range-2 then do;
do j=2 to count;
_year{j}=_year{j}+1;
end;
end;
output;
end;
run;

%let year=2007 ; * the year you want;
data _null_;
set year(where=(year=&year));
array _year{*} year1-year&range;
do i=1 to &range-1;
call symputx(cats('year',i),_year{i});
end;
run;

%put _user_;
[/pre]

[pre]

Ksharp Message was edited by: Ksharp