BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ruslan
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Urban_Science
Quartz | Level 8

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;

 

View solution in original post

1 REPLY 1
Urban_Science
Quartz | Level 8

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;

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 1534 views
  • 0 likes
  • 2 in conversation