topic read a excel file into SAS with a specific format in SAS Programming https://communities.sas.com/t5/SAS-Programming/read-a-excel-file-into-SAS-with-a-specific-format/m-p/506878#M135915 <P>Dear All,</P><P>&nbsp;</P><P>Could you please give me some suggestion to&nbsp;record the following excel A as table&nbsp;B in the SAS,</P><P>&nbsp;</P><P>Excel A</P><P>&nbsp;</P><TABLE><TBODY><TR><TD><P>Name</P></TD><TD>MOOG 'A'</TD><TD>BOEING</TD><TD>UNITED TECHNOLOGIES</TD></TR><TR><TD>Code</TD><TD>U:MOG.A(P)</TD><TD>U:BA(P)</TD><TD>U:UTX(P)</TD></TR><TR><TD>CURRENCY</TD><TD>U$</TD><TD>U$</TD><TD>U$</TD></TR><TR><TD>28/05/1980</TD><TD>2.0494</TD><TD>4.9817</TD><TD>2.6562</TD></TR><TR><TD>29/05/1980</TD><TD>2.0494</TD><TD>4.8891</TD><TD>2.625</TD></TR><TR><TD>30/05/1980</TD><TD>2</TD><TD>5.0929</TD><TD>2.6719</TD></TR><TR><TD>02/06/1980</TD><TD>2</TD><TD>5.1484</TD><TD>2.7344</TD></TR><TR><TD>03/06/1980</TD><TD>1.9753</TD><TD>5.1484</TD><TD>2.7344</TD></TR><TR><TD>04/06/1980</TD><TD>1.9753</TD><TD>5.278</TD><TD>2.7969</TD></TR><TR><TD>05/06/1980</TD><TD>1.9753</TD><TD>5.1669</TD><TD>2.7812</TD></TR><TR><TD>06/06/1980</TD><TD>1.9753</TD><TD>5.0558</TD><TD>2.7656</TD></TR><TR><TD>09/06/1980</TD><TD>1.9753</TD><TD>5.0743</TD><TD>2.7109</TD></TR><TR><TD>10/06/1980</TD><TD>1.9753</TD><TD>5.1855</TD><TD>2.6797</TD></TR><TR><TD>11/06/1980</TD><TD>1.9753</TD><TD>5.4632</TD><TD>2.7344</TD></TR><TR><TD>12/06/1980</TD><TD>2</TD><TD>5.3706</TD><TD>2.6797</TD></TR><TR><TD>13/06/1980</TD><TD>2</TD><TD>5.4632</TD><TD>2.7031</TD></TR><TR><TD>16/06/1980</TD><TD>2</TD><TD>5.4447</TD><TD>2.7266</TD></TR><TR><TD>17/06/1980</TD><TD>2.0247</TD><TD>5.3706</TD><TD>2.7109</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P><SPAN>Table&nbsp;B</SPAN></P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Name</TD><TD>Code</TD><TD>CURRENCY</TD><TD>DATE</TD><TD>Price</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>28/05/1980</TD><TD>2.0494</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>29/05/1980</TD><TD>2.0494</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>30/05/1980</TD><TD>2</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>02/06/1980</TD><TD>2</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>03/06/1980</TD><TD>1.9753</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>04/06/1980</TD><TD>1.9753</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>05/06/1980</TD><TD>1.9753</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>06/06/1980</TD><TD>1.9753</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>09/06/1980</TD><TD>1.9753</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>10/06/1980</TD><TD>1.9753</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>11/06/1980</TD><TD>1.9753</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>12/06/1980</TD><TD>2</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>13/06/1980</TD><TD>2</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>16/06/1980</TD><TD>2</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>17/06/1980</TD><TD>2.0247</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>28/05/1980</TD><TD>4.9817</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>29/05/1980</TD><TD>4.8891</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>30/05/1980</TD><TD>5.0929</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>02/06/1980</TD><TD>5.1484</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>03/06/1980</TD><TD>5.1484</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>04/06/1980</TD><TD>5.278</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>05/06/1980</TD><TD>5.1669</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>06/06/1980</TD><TD>5.0558</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>09/06/1980</TD><TD>5.0743</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>10/06/1980</TD><TD>5.1855</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>11/06/1980</TD><TD>5.4632</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>12/06/1980</TD><TD>5.3706</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>13/06/1980</TD><TD>5.4632</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>16/06/1980</TD><TD>5.4447</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>17/06/1980</TD><TD>5.3706</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>28/05/1980</TD><TD>2.6562</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>29/05/1980</TD><TD>2.625</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>30/05/1980</TD><TD>2.6719</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>02/06/1980</TD><TD>2.7344</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>03/06/1980</TD><TD>2.7344</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>04/06/1980</TD><TD>2.7969</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>05/06/1980</TD><TD>2.7812</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>06/06/1980</TD><TD>2.7656</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>09/06/1980</TD><TD>2.7109</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>10/06/1980</TD><TD>2.6797</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>11/06/1980</TD><TD>2.7344</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>12/06/1980</TD><TD>2.6797</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>13/06/1980</TD><TD>2.7031</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>16/06/1980</TD><TD>2.7266</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>17/06/1980</TD><TD>2.7109</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>The excel&nbsp;A is added in the attachment.</P><P>thanks in advance.</P><P>best regards,</P><P>lipeng&nbsp;</P> Tue, 23 Oct 2018 15:33:53 GMT France 2018-10-23T15:33:53Z read a excel file into SAS with a specific format https://communities.sas.com/t5/SAS-Programming/read-a-excel-file-into-SAS-with-a-specific-format/m-p/506878#M135915 <P>Dear All,</P><P>&nbsp;</P><P>Could you please give me some suggestion to&nbsp;record the following excel A as table&nbsp;B in the SAS,</P><P>&nbsp;</P><P>Excel A</P><P>&nbsp;</P><TABLE><TBODY><TR><TD><P>Name</P></TD><TD>MOOG 'A'</TD><TD>BOEING</TD><TD>UNITED TECHNOLOGIES</TD></TR><TR><TD>Code</TD><TD>U:MOG.A(P)</TD><TD>U:BA(P)</TD><TD>U:UTX(P)</TD></TR><TR><TD>CURRENCY</TD><TD>U$</TD><TD>U$</TD><TD>U$</TD></TR><TR><TD>28/05/1980</TD><TD>2.0494</TD><TD>4.9817</TD><TD>2.6562</TD></TR><TR><TD>29/05/1980</TD><TD>2.0494</TD><TD>4.8891</TD><TD>2.625</TD></TR><TR><TD>30/05/1980</TD><TD>2</TD><TD>5.0929</TD><TD>2.6719</TD></TR><TR><TD>02/06/1980</TD><TD>2</TD><TD>5.1484</TD><TD>2.7344</TD></TR><TR><TD>03/06/1980</TD><TD>1.9753</TD><TD>5.1484</TD><TD>2.7344</TD></TR><TR><TD>04/06/1980</TD><TD>1.9753</TD><TD>5.278</TD><TD>2.7969</TD></TR><TR><TD>05/06/1980</TD><TD>1.9753</TD><TD>5.1669</TD><TD>2.7812</TD></TR><TR><TD>06/06/1980</TD><TD>1.9753</TD><TD>5.0558</TD><TD>2.7656</TD></TR><TR><TD>09/06/1980</TD><TD>1.9753</TD><TD>5.0743</TD><TD>2.7109</TD></TR><TR><TD>10/06/1980</TD><TD>1.9753</TD><TD>5.1855</TD><TD>2.6797</TD></TR><TR><TD>11/06/1980</TD><TD>1.9753</TD><TD>5.4632</TD><TD>2.7344</TD></TR><TR><TD>12/06/1980</TD><TD>2</TD><TD>5.3706</TD><TD>2.6797</TD></TR><TR><TD>13/06/1980</TD><TD>2</TD><TD>5.4632</TD><TD>2.7031</TD></TR><TR><TD>16/06/1980</TD><TD>2</TD><TD>5.4447</TD><TD>2.7266</TD></TR><TR><TD>17/06/1980</TD><TD>2.0247</TD><TD>5.3706</TD><TD>2.7109</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P><SPAN>Table&nbsp;B</SPAN></P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Name</TD><TD>Code</TD><TD>CURRENCY</TD><TD>DATE</TD><TD>Price</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>28/05/1980</TD><TD>2.0494</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>29/05/1980</TD><TD>2.0494</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>30/05/1980</TD><TD>2</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>02/06/1980</TD><TD>2</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>03/06/1980</TD><TD>1.9753</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>04/06/1980</TD><TD>1.9753</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>05/06/1980</TD><TD>1.9753</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>06/06/1980</TD><TD>1.9753</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>09/06/1980</TD><TD>1.9753</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>10/06/1980</TD><TD>1.9753</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>11/06/1980</TD><TD>1.9753</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>12/06/1980</TD><TD>2</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>13/06/1980</TD><TD>2</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>16/06/1980</TD><TD>2</TD></TR><TR><TD>MOOG 'A'</TD><TD>U:MOG.A(P)</TD><TD>U$</TD><TD>17/06/1980</TD><TD>2.0247</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>28/05/1980</TD><TD>4.9817</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>29/05/1980</TD><TD>4.8891</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>30/05/1980</TD><TD>5.0929</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>02/06/1980</TD><TD>5.1484</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>03/06/1980</TD><TD>5.1484</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>04/06/1980</TD><TD>5.278</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>05/06/1980</TD><TD>5.1669</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>06/06/1980</TD><TD>5.0558</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>09/06/1980</TD><TD>5.0743</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>10/06/1980</TD><TD>5.1855</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>11/06/1980</TD><TD>5.4632</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>12/06/1980</TD><TD>5.3706</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>13/06/1980</TD><TD>5.4632</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>16/06/1980</TD><TD>5.4447</TD></TR><TR><TD>BOEING</TD><TD>U:BA(P)</TD><TD>U$</TD><TD>17/06/1980</TD><TD>5.3706</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>28/05/1980</TD><TD>2.6562</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>29/05/1980</TD><TD>2.625</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>30/05/1980</TD><TD>2.6719</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>02/06/1980</TD><TD>2.7344</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>03/06/1980</TD><TD>2.7344</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>04/06/1980</TD><TD>2.7969</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>05/06/1980</TD><TD>2.7812</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>06/06/1980</TD><TD>2.7656</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>09/06/1980</TD><TD>2.7109</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>10/06/1980</TD><TD>2.6797</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>11/06/1980</TD><TD>2.7344</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>12/06/1980</TD><TD>2.6797</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>13/06/1980</TD><TD>2.7031</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>16/06/1980</TD><TD>2.7266</TD></TR><TR><TD>UNITED TECHNOLOGIES</TD><TD>U:UTX(P)</TD><TD>U$</TD><TD>17/06/1980</TD><TD>2.7109</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>The excel&nbsp;A is added in the attachment.</P><P>thanks in advance.</P><P>best regards,</P><P>lipeng&nbsp;</P> Tue, 23 Oct 2018 15:33:53 GMT https://communities.sas.com/t5/SAS-Programming/read-a-excel-file-into-SAS-with-a-specific-format/m-p/506878#M135915 France 2018-10-23T15:33:53Z Re: read a excel file into SAS with a specific format https://communities.sas.com/t5/SAS-Programming/read-a-excel-file-into-SAS-with-a-specific-format/m-p/506880#M135917 Read the first data set twice, once to get everything from row 2 down and the second time to get the first two rows only. Transpose both using PROC TRANSPOSE and then merge the results. Tue, 23 Oct 2018 15:37:02 GMT https://communities.sas.com/t5/SAS-Programming/read-a-excel-file-into-SAS-with-a-specific-format/m-p/506880#M135917 Reeza 2018-10-23T15:37:02Z