topic Re: unblanced to balanced panel? in New SAS User https://communities.sas.com/t5/New-SAS-User/unblanced-to-balanced-panel/m-p/851120#M37318 <PRE><CODE class=" language-sas">data have; infile cards expandtabs truncover; input company_name &amp;$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;</CODE></PRE> Mon, 26 Dec 2022 10:34:44 GMT Ksharp 2022-12-26T10:34:44Z unblanced to balanced panel? https://communities.sas.com/t5/New-SAS-User/unblanced-to-balanced-panel/m-p/851065#M37303 <P>dear all&nbsp;</P><P>i have the panel data in unbalanced form.&nbsp; 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.&nbsp;</P><P>&nbsp;</P><P>the following is my data format&nbsp;</P><TABLE><TBODY><TR><TD>company_name</TD><TD>year</TD><TD>var</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2011</TD><TD>0.805904</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2012</TD><TD>0.828198</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2013</TD><TD>0.817836</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2016</TD><TD>0.755992</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2017</TD><TD>0.773837</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2018</TD><TD>0.725707</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2019</TD><TD>0.695296</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2020</TD><TD>0.656195</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2021</TD><TD>0.580448</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2011</TD><TD>5.643716</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2013</TD><TD>6.415471</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2014</TD><TD>8.262973</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2015</TD><TD>7.151063</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2016</TD><TD>3.419378</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2017</TD><TD>4.005865</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2019</TD><TD>6.123732</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2020</TD><TD>5.872237</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2021</TD><TD>5.987689</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>I need the output in the following format&nbsp;</P><TABLE><TBODY><TR><TD>company_name</TD><TD>year</TD><TD>var</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2011</TD><TD>0.805904</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2012</TD><TD>0.828198</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2013</TD><TD>0.817836</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2014</TD><TD>0.737712</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2015</TD><TD>0.737712</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2016</TD><TD>0.755992</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2017</TD><TD>0.773837</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2018</TD><TD>0.725707</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2019</TD><TD>0.695296</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2020</TD><TD>0.656195</TD></TR><TR><TD>20 Microns Ltd.</TD><TD>2021</TD><TD>0.580448</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2011</TD><TD>5.643716</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2012</TD><TD>5.875792</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2013</TD><TD>6.415471</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2014</TD><TD>8.262973</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2015</TD><TD>7.151063</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2016</TD><TD>3.419378</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2017</TD><TD>4.005865</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2018</TD><TD>5.875792</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2019</TD><TD>6.123732</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2020</TD><TD>5.872237</TD></TR><TR><TD>3I Infotech Ltd.</TD><TD>2021</TD><TD>5.987689</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>please suggest to me a SAS code for mean imputation in panel data&nbsp;</P><P>thanking you in advance&nbsp;</P> Sun, 25 Dec 2022 12:53:12 GMT https://communities.sas.com/t5/New-SAS-User/unblanced-to-balanced-panel/m-p/851065#M37303 srikanthyadav44 2022-12-25T12:53:12Z Re: unblanced to balanced panel? https://communities.sas.com/t5/New-SAS-User/unblanced-to-balanced-panel/m-p/851067#M37304 <P>Why is the value for "20 Microns" different in 2018 than in 2014 and 2015, when it should always be the mean over the whole span 2011 to 2021?</P> Sun, 25 Dec 2022 13:14:08 GMT https://communities.sas.com/t5/New-SAS-User/unblanced-to-balanced-panel/m-p/851067#M37304 Kurt_Bremser 2022-12-25T13:14:08Z Re: unblanced to balanced panel? https://communities.sas.com/t5/New-SAS-User/unblanced-to-balanced-panel/m-p/851120#M37318 <PRE><CODE class=" language-sas">data have; infile cards expandtabs truncover; input company_name &amp;$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;</CODE></PRE> Mon, 26 Dec 2022 10:34:44 GMT https://communities.sas.com/t5/New-SAS-User/unblanced-to-balanced-panel/m-p/851120#M37318 Ksharp 2022-12-26T10:34:44Z