topic Re: how to impute the missing values ? in New SAS User

how to impute the missing values ?

srikanthyadav44 — Thu, 05 Sep 2019 08:34:47 GMT

dear all

I have to impute the missing values with the immediately preceeding value in the series.

earlier, i have done mean imputation by using the following code.

What changes i have to do to the following SAS code, (which i have got from a SAS blog), to make imputation with preceeding values.

thanks in advance

/* Mean imputation: Use PROC STDIZE to replace missing values with mean */
proc stdize data=Have out=Imputed 
      oprefix=Orig_         /* prefix for original variables */
      reponly               /* only replace; do not standardize */
      method=MEAN;          /* or MEDIAN, MINIMUM, MIDRANGE, etc. */
   var Height;              /* you can list multiple variables to impute */
run;

Re: how to impute the missing values ?

PeterClemmensen — Thu, 05 Sep 2019 08:53:39 GMT

PROC STDIZE is not the tool for this job. Post some sample data is you want a usable code answer.

Re: how to impute the missing values ?

srikanthyadav44 — Thu, 05 Sep 2019 10:55:49 GMT

dear draycut

the format of the dataset is as follows

it is a panel data .

company_name	time	investment
3I Infotech Ltd.	200703	24.03
3I Infotech Ltd.	200706	27.29
3I Infotech Ltd.	200709
3I Infotech Ltd.	200712	31.73
3I Infotech Ltd.	200803	33.39
3I Infotech Ltd.	200806	33.2
3I Infotech Ltd.	200809	32.46
3I Infotech Ltd.	200812	28.48
3M India Ltd.	200703	24.11
3M India Ltd.	200706
3M India Ltd.	200709	39.14
3M India Ltd.	200712	35.99
3M India Ltd.	200803	39.03
3M India Ltd.	200806
3M India Ltd.	200809	37.34
3M India Ltd.	200812	40.36

please suggest the SAS code for it.

thanks in advance

Re: how to impute the missing values ?

PeterClemmensen — Thu, 05 Sep 2019 11:05:00 GMT

Assuming your data is sorted by company_name

data have;
input company_name $ 1-17 time :yymmn6. investment;
infile datalines missover;
format time yymmn6.;
datalines;
3I Infotech Ltd. 200703 24.03
3I Infotech Ltd. 200706 27.29
3I Infotech Ltd. 200709      
3I Infotech Ltd. 200712 31.73
3I Infotech Ltd. 200803 33.39
3I Infotech Ltd. 200806 33.2
3I Infotech Ltd. 200809 32.46
3I Infotech Ltd. 200812 28.48
3M India Ltd.    200703 24.11
3M India Ltd.    200706      
3M India Ltd.    200709 39.14
3M India Ltd.    200712 35.99
3M India Ltd.    200803 39.03
3M India Ltd.    200806      
3M India Ltd.    200809 37.34
3M India Ltd.    200812 40.36
;

data Want;
   update have(obs=0) have;
   by company_name;
   output;
run;

Re: how to impute the missing values ?

srikanthyadav44 — Thu, 05 Sep 2019 17:16:35 GMT

dear draycut

thanks a lot for your prompt response.

Your SAS code is working excellently on the sample data posted in the query.

In fact, I have the quarterly data from 200703 to 201803 having 45 observations for each companies and the panel dataset is having nearly 5000 companies.

So, the total number of rows are more than 2 lakhs. the data is in a excel file.

I am unable to change your code to run it with the imported excel file in SAS.

please guide me making the required changes to the code sugggested by you.

thanks in advance

Re: how to impute the missing values ?

srikanthyadav44 — Mon, 30 Sep 2019 08:53:44 GMT

thank Mr. draycut

the SAS code is working good.