BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.

dear all 

i have the panel data in unbalanced form.  i have to convert it into balanced form by imputing the missing values with mean of the respective company from the time period 2011 to 2021. 

 

the following is my data format 

company_nameyearvar
20 Microns Ltd.20110.805904
20 Microns Ltd.20120.828198
20 Microns Ltd.20130.817836
20 Microns Ltd.20160.755992
20 Microns Ltd.20170.773837
20 Microns Ltd.20180.725707
20 Microns Ltd.20190.695296
20 Microns Ltd.20200.656195
20 Microns Ltd.20210.580448
3I Infotech Ltd.20115.643716
3I Infotech Ltd.20136.415471
3I Infotech Ltd.20148.262973
3I Infotech Ltd.20157.151063
3I Infotech Ltd.20163.419378
3I Infotech Ltd.20174.005865
3I Infotech Ltd.20196.123732
3I Infotech Ltd.20205.872237
3I Infotech Ltd.20215.987689

 

I need the output in the following format 

company_nameyearvar
20 Microns Ltd.20110.805904
20 Microns Ltd.20120.828198
20 Microns Ltd.20130.817836
20 Microns Ltd.20140.737712
20 Microns Ltd.20150.737712
20 Microns Ltd.20160.755992
20 Microns Ltd.20170.773837
20 Microns Ltd.20180.725707
20 Microns Ltd.20190.695296
20 Microns Ltd.20200.656195
20 Microns Ltd.20210.580448
3I Infotech Ltd.20115.643716
3I Infotech Ltd.20125.875792
3I Infotech Ltd.20136.415471
3I Infotech Ltd.20148.262973
3I Infotech Ltd.20157.151063
3I Infotech Ltd.20163.419378
3I Infotech Ltd.20174.005865
3I Infotech Ltd.20185.875792
3I Infotech Ltd.20196.123732
3I Infotech Ltd.20205.872237
3I Infotech Ltd.20215.987689

 

please suggest to me a SAS code for mean imputation in panel data 

thanking you in advance 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
data have;
infile cards expandtabs truncover;
input company_name &$20.	year	var;
cards;
20 Microns Ltd. 	2011	0.805904
20 Microns Ltd. 	2012	0.828198
20 Microns Ltd.	 2013	0.817836
20 Microns Ltd.	 2016	0.755992
20 Microns Ltd.	 2017	0.773837
20 Microns Ltd.	 2018	0.725707
20 Microns Ltd. 	2019	0.695296
20 Microns Ltd.	  2020	0.656195
20 Microns Ltd. 	2021	0.580448
3I Infotech Ltd.	2011	5.643716
3I Infotech Ltd.	2013	6.415471
3I Infotech Ltd.	2014	8.262973
3I Infotech Ltd.	2015	7.151063
3I Infotech Ltd.	2016	3.419378
3I Infotech Ltd.	2017	4.005865
3I Infotech Ltd.	2019	6.123732
3I Infotech Ltd.	2020	5.872237
3I Infotech Ltd.	2021	5.987689
;
proc sql;
create table temp as
select company_name,min(year) as min,max(year) as max
 from have
  group by company_name;
quit;
data temp2;
 set temp;
 do year=min to max;
   output;
 end;
 keep company_name year;
run;
proc sql;
create table temp3 as
select a.*,b.var
 from temp2 as a natural left join have as b;
quit;
proc stdize data=temp3 out=want missing=mean reponly;
by company_name;
var var;
run;

View solution in original post

2 REPLIES 2
Ksharp
Super User
data have;
infile cards expandtabs truncover;
input company_name &$20.	year	var;
cards;
20 Microns Ltd. 	2011	0.805904
20 Microns Ltd. 	2012	0.828198
20 Microns Ltd.	 2013	0.817836
20 Microns Ltd.	 2016	0.755992
20 Microns Ltd.	 2017	0.773837
20 Microns Ltd.	 2018	0.725707
20 Microns Ltd. 	2019	0.695296
20 Microns Ltd.	  2020	0.656195
20 Microns Ltd. 	2021	0.580448
3I Infotech Ltd.	2011	5.643716
3I Infotech Ltd.	2013	6.415471
3I Infotech Ltd.	2014	8.262973
3I Infotech Ltd.	2015	7.151063
3I Infotech Ltd.	2016	3.419378
3I Infotech Ltd.	2017	4.005865
3I Infotech Ltd.	2019	6.123732
3I Infotech Ltd.	2020	5.872237
3I Infotech Ltd.	2021	5.987689
;
proc sql;
create table temp as
select company_name,min(year) as min,max(year) as max
 from have
  group by company_name;
quit;
data temp2;
 set temp;
 do year=min to max;
   output;
 end;
 keep company_name year;
run;
proc sql;
create table temp3 as
select a.*,b.var
 from temp2 as a natural left join have as b;
quit;
proc stdize data=temp3 out=want missing=mean reponly;
by company_name;
var var;
run;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 699 views
  • 0 likes
  • 3 in conversation