Retain values across row

Reply
Super User
Posts: 19,063

Retain values across row

[ Edited ]

Posting this for someone who was unable to post in the forum @Ruslan

I have the dataset that looks like this:

 

Date      Company    Var

 

199601        1             .

199602        1             .

199603        1             .

199604        1             .

199605        1             .

199606        1             10

199607        1             .

199608        1             .

199609        1             .

199610        1             .

199611        1             . 

199612        1             .

199701        1             .

199702        1             .

199703        1             .

199704        1             .

199705        1             .

199706        1             11

199707        1             .

199708        1             .

199709        1             .

199710        1             .

199711        1             .

199712        1             .

 

The required dataset should look like this:

 

Date      Company    Var

 

199601        1             .

199602        1             .

199603        1             .

199604        1             .

199605        1             .

199606        1             .

199607        1             10

199608        1             10

199609        1             10

199610        1             10

199611        1             10

199612        1             10

199701        1             10

199702        1             10

199703        1             10

199704        1             10

199705        1             10

199706        1             10

199707        1             11

199708        1             11

199709        1             11

199710        1             11

199711        1             11

199712        1             11

 

So what I need to do is for each company, to repeat the same value that is available in June, year t, for next months within a year until June, year t+1. Please note that I need to use Var value in June, but to start from July, year t, and repeat the value until June, year t+1 and so on. I have many months and many companies.

 

Super Contributor
Posts: 414

Re: Retain values across row

How about this?

 

data want;
	set have;
	retain var_save;
	if var=. then var=var_save;
	else var_save=var;
	drop var_save;
run;

Hope this helps,

- Jan.

Super Contributor
Posts: 414

Re: Retain values across row

Oh no. Missed the requirements by a long shot. Oops.
Super User
Posts: 5,360

Re: Retain values across row

I think this works, but I can't test it:

 

data want;

last_june_var = var;

do until (var > . or last.company);

   set have;

   by company;

   output;

end;

if last.company than last_june_var = .;

drop var;

rename last_June_var = var;

run;

 

It's easier when VAR is numeric, but could be adapted if VAR is character.

Super User
Posts: 9,865

Re: Retain values across row

data have;
input Date      Company    Var;
cards;
199601        1             .
199602        1             .
199603        1             .
199604        1             .
199605        1             .
199606        1             10
199607        1             .
199608        1             .
199609        1             .
199610        1             .
199611        1             . 
199612        1             .
199701        1             .
199702        1             .
199703        1             .
199704        1             .
199705        1             .
199706        1             11
199707        1             .
199708        1             .
199709        1             .
199710        1             .
199711        1             .
199712        1             .
;
run;
data temp;
 set have;
 retain new_var;
 if not missing(var) then do;
  flag=1;
  new_var=var;
 end;
drop var;
run;

data want;
 set temp;
 lag_var=lag(new_var);
 if flag then new_var=lag_var;
 drop lag_var flag;
run;
Ask a Question
Discussion stats
  • 4 replies
  • 391 views
  • 0 likes
  • 4 in conversation