DATA Step, Macro, Functions and more

Help on Assigning Values Based on Previous and Future Values

Reply
Contributor
Posts: 24

Help on Assigning Values Based on Previous and Future Values

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;
Valued Guide
Posts: 2,175

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

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
Contributor
Posts: 24

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

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.
Contributor
Posts: 24

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

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;
Valued Guide
Posts: 2,175

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

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 ;
Contributor
Posts: 24

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

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
Regular Contributor
Posts: 241

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

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


      %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


   --*;

Valued Guide
Posts: 2,175

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

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
Contributor
Posts: 24

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

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
Super User
Posts: 9,681

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

Ksharp Message was edited by: Ksharp
Regular Contributor
Posts: 241

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

@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)
Super User
Posts: 9,681

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

Sorry.My code is wrong.I did not completely notice what OP want.
Super User
Posts: 9,681

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

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
Ask a Question
Discussion stats
  • 12 replies
  • 269 views
  • 0 likes
  • 4 in conversation