Repeat the same value for next 12 months

Ruslan — Thu, 30 Jun 2016 13:57:31 GMT

Hi All,

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. I have many months and many companies.

Any help will be hugely appriciated.

Kind regards,

Ruslan

Re: Repeat the same value for next 12 months

Urban_Science — Fri, 01 Jul 2016 15:09:14 GMT

I would recommend using the retain statement with a temporary variable. Simply put, any variables specified in the retain statement are not reset from one itteration of the data step to the next.

data outputData (drop=temp_var temp_company); /* Drops temp_var and temp_company after the data step has completely finished running */
	set inputData;
	retain temp_var . temp_company .; /* Initializes temp_var to missing */

        /* When the company changes, reset temp_var to missing */
	if temp_company ne company then do;
		temp_company = company;
		temp_var = .;
	end;

	/* Check to see if the variable Var is missing */
	if Var = . then Var = temp_var; /* When missing, assign it to the value of temp_var */
	else temp_var = Var; /* otherwise assign temp_var the value of Var */
run;

EDIT: I noticed that you also mentioned that this needs to work for multiple companies, I've changed my code to handle that by retaining the previous company and resetting temp_var when the company changes.

data inputData;
	input Date      Company    Var;
	datalines;
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             .
199601        2             .
199602        2             .
199603        2             .
199604        2             .
199605        2             .
199606        2             10
199607        2             .
199608        2             .
199609        2             .
199610        2             .
199611        2             . 
199612        2             .
199701        2             .
199702        2             .
199703        2             .
199704        2             .
199705        2             .
199706        2             11
199707        2             .
199708        2             .
199709        2             .
199710        2             .
199711        2             .
199712        2             .
;
run;

data outputData (drop=temp_var temp_company); /* Drops temp_var and temp_company after the data step has completely finished running */
	set inputData;
	retain temp_var . temp_company .; /* Initializes temp_var to missing */

        /* When the company changes, reset temp_var to missing */
	if temp_company ne company then do;
		temp_company = company;
		temp_var = .;
	end;

	/* Check to see if the variable Var is missing */
	if Var = . then Var = temp_var; /* When missing, assign it to the value of temp_var */
	else temp_var = Var; /* otherwise assign temp_var the value of Var */
run;

topic Repeat the same value for next 12 months in SAS Programming

Repeat the same value for next 12 months

Re: Repeat the same value for next 12 months