topic Help needed to do a loop/array to create a new table in New SAS User

topic Help needed to do a loop/array to create a new table in New SAS User https://communities.sas.com/t5/New-SAS-User/Help-needed-to-do-a-loop-array-to-create-a-new-table/m-p/537936#M6847 <P>Hi,</P><P> </P><P>I'm writting a loop to create a new table as following and got stacked. </P><P>Here is an example table with a table of scores for different months. There are date_1 and Date_2(table #1).</P><P>I need to create a new table(table #2) to only keep the scores from date_1 to date_2, and in line with date_2(as Mon_0).</P><P>Is there a way to write a do loop/array to solve the problem? Really appreciate.</P><P> </P><P>Table #1</P><TABLE><TBODY><TR><TD>ID</TD><TD>date_1</TD><TD>date_2</TD><TD>sc_201501</TD><TD>sc_201502</TD><TD>sc_201503</TD><TD>sc_201504</TD><TD>sc_201505</TD><TD>sc_201506</TD><TD>sc_201507</TD><TD>sc_201508</TD><TD>sc_201509</TD><TD>sc_201510</TD><TD>sc_201511</TD><TD>sc_201512</TD><TD>sc_201601</TD><TD>sc_201602</TD><TD>sc_201603</TD><TD>sc_201604</TD><TD>sc_201605</TD><TD>sc_201606</TD><TD>sc_201607</TD><TD>sc_201608</TD><TD>sc_201609</TD><TD>sc_201610</TD><TD>sc_201611</TD><TD>sc_201612</TD></TR><TR><TD>1</TD><TD>2015_01</TD><TD>2016_02</TD><TD>0.658</TD><TD>0.888</TD><TD>0.330</TD><TD>0.938</TD><TD>0.148</TD><TD>0.062</TD><TD>0.598</TD><TD>0.944</TD><TD>0.770</TD><TD>0.189</TD><TD>0.144</TD><TD>0.842</TD><TD>0.372</TD><TD>0.481</TD><TD>0.123</TD><TD>0.630</TD><TD>0.276</TD><TD>0.292</TD><TD>0.626</TD><TD>0.665</TD><TD>0.885</TD><TD>0.614</TD><TD>0.331</TD><TD>0.467</TD></TR><TR><TD>2</TD><TD>2016_01</TD><TD>2016_12</TD><TD>0.414</TD><TD>0.915</TD><TD>0.748</TD><TD>0.160</TD><TD>0.388</TD><TD>0.100</TD><TD>0.751</TD><TD>0.089</TD><TD>0.786</TD><TD>0.126</TD><TD>0.305</TD><TD>0.852</TD><TD>0.874</TD><TD>0.875</TD><TD>0.414</TD><TD>0.404</TD><TD>0.268</TD><TD>0.730</TD><TD>0.760</TD><TD>0.443</TD><TD>0.577</TD><TD>0.231</TD><TD>0.641</TD><TD>0.398</TD></TR><TR><TD>3</TD><TD>2016_04</TD><TD>2016_12</TD><TD>0.614</TD><TD>0.166</TD><TD>0.465</TD><TD>0.347</TD><TD>0.680</TD><TD>0.042</TD><TD>0.400</TD><TD>0.959</TD><TD>0.223</TD><TD>0.603</TD><TD>0.918</TD><TD>0.954</TD><TD>0.289</TD><TD>0.480</TD><TD>0.626</TD><TD>0.638</TD><TD>0.451</TD><TD>0.478</TD><TD>0.197</TD><TD>0.175</TD><TD>0.093</TD><TD>0.096</TD><TD>0.535</TD><TD>0.990</TD></TR><TR><TD>4</TD><TD>2016_03</TD><TD>2016_11</TD><TD>0.054</TD><TD>0.871</TD><TD>0.087</TD><TD>0.286</TD><TD>0.801</TD><TD>0.701</TD><TD>0.617</TD><TD>0.015</TD><TD>0.215</TD><TD>0.546</TD><TD>0.467</TD><TD>0.219</TD><TD>0.561</TD><TD>0.397</TD><TD>0.878</TD><TD>0.481</TD><TD>0.418</TD><TD>0.887</TD><TD>0.758</TD><TD>0.575</TD><TD>0.750</TD><TD>0.364</TD><TD>0.898</TD><TD>0.437</TD></TR><TR><TD>5</TD><TD>2015_12</TD><TD>2016_05</TD><TD>0.888</TD><TD>0.453</TD><TD>0.649</TD><TD>0.617</TD><TD>0.734</TD><TD>0.244</TD><TD>0.512</TD><TD>0.033</TD><TD>0.964</TD><TD>0.416</TD><TD>0.739</TD><TD>0.183</TD><TD>0.665</TD><TD>0.588</TD><TD>0.962</TD><TD>0.755</TD><TD>0.500</TD><TD>0.236</TD><TD>0.983</TD><TD>0.843</TD><TD>0.256</TD><TD>0.489</TD><TD>0.394</TD><TD>0.682</TD></TR><TR><TD>6</TD><TD>2015_10</TD><TD>2016_03</TD><TD>0.517</TD><TD>0.493</TD><TD>0.239</TD><TD>0.960</TD><TD>0.050</TD><TD>0.082</TD><TD>0.701</TD><TD>0.576</TD><TD>0.892</TD><TD>0.446</TD><TD>0.167</TD><TD>0.467</TD><TD>0.085</TD><TD>0.047</TD><TD>0.492</TD><TD>0.837</TD><TD>0.289</TD><TD>0.107</TD><TD>0.840</TD><TD>0.442</TD><TD>0.951</TD><TD>0.785</TD><TD>0.169</TD><TD>0.173</TD></TR><TR><TD>7</TD><TD>2015_02</TD><TD>2015_12</TD><TD>0.206</TD><TD>0.587</TD><TD>0.518</TD><TD>0.233</TD><TD>0.906</TD><TD>0.983</TD><TD>0.273</TD><TD>0.845</TD><TD>0.600</TD><TD>0.514</TD><TD>0.920</TD><TD>0.182</TD><TD>0.433</TD><TD>0.026</TD><TD>0.948</TD><TD>0.281</TD><TD>0.347</TD><TD>0.272</TD><TD>0.818</TD><TD>0.054</TD><TD>0.415</TD><TD>0.759</TD><TD>0.547</TD><TD>0.319</TD></TR></TBODY></TABLE><P> </P><P>Table #2</P><TABLE><TBODY><TR><TD>ID</TD><TD>date_1</TD><TD>date_2</TD><TD>Mon_before14</TD><TD>Mon_before13</TD><TD>Mon_before12</TD><TD>Mon_before11</TD><TD>Mon_before10</TD><TD>Mon_before9</TD><TD>Mon_before8</TD><TD>Mon_before7</TD><TD>Mon_before6</TD><TD>Mon_before5</TD><TD>Mon_before4</TD><TD>Mon_before3</TD><TD>Mon_before2</TD><TD>Mon_before1</TD><TD>Mon_0</TD></TR><TR><TD>1</TD><TD>2015_01</TD><TD>2016_02</TD><TD> </TD><TD>0.658</TD><TD>0.888</TD><TD>0.330</TD><TD>0.938</TD><TD>0.148</TD><TD>0.062</TD><TD>0.598</TD><TD>0.944</TD><TD>0.770</TD><TD>0.189</TD><TD>0.144</TD><TD>0.842</TD><TD>0.372</TD><TD>0.481</TD></TR><TR><TD>2</TD><TD>2016_01</TD><TD>2016_12</TD><TD> </TD><TD> </TD><TD> </TD><TD>0.874</TD><TD>0.875</TD><TD>0.414</TD><TD>0.404</TD><TD>0.268</TD><TD>0.730</TD><TD>0.760</TD><TD>0.443</TD><TD>0.577</TD><TD>0.231</TD><TD>0.641</TD><TD>0.398</TD></TR><TR><TD>3</TD><TD>2016_04</TD><TD>2016_12</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>0.638</TD><TD>0.451</TD><TD>0.478</TD><TD>0.197</TD><TD>0.175</TD><TD>0.093</TD><TD>0.096</TD><TD>0.535</TD><TD>0.990</TD></TR><TR><TD>4</TD><TD>2016_03</TD><TD>2016_11</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>0.878</TD><TD>0.481</TD><TD>0.418</TD><TD>0.887</TD><TD>0.758</TD><TD>0.575</TD><TD>0.750</TD><TD>0.364</TD><TD>0.898</TD></TR><TR><TD>5</TD><TD>2015_12</TD><TD>2016_05</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>0.183</TD><TD>0.665</TD><TD>0.588</TD><TD>0.962</TD><TD>0.755</TD><TD>0.500</TD></TR><TR><TD>6</TD><TD>2015_10</TD><TD>2016_03</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>0.446</TD><TD>0.167</TD><TD>0.467</TD><TD>0.085</TD><TD>0.047</TD><TD>0.492</TD></TR><TR><TD>7</TD><TD>2015_02</TD><TD>2015_12</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>0.587</TD><TD>0.518</TD><TD>0.233</TD><TD>0.906</TD><TD>0.983</TD><TD>0.273</TD><TD>0.845</TD><TD>0.600</TD><TD>0.514</TD><TD>0.920</TD><TD>0.182</TD></TR></TBODY></TABLE><P> </P><P>Many thanks,</P><P>Olivia<BR /> </P> Sat, 23 Feb 2019 05:24:27 GMT oliviameng 2019-02-23T05:24:27Z Help needed to do a loop/array to create a new table https://communities.sas.com/t5/New-SAS-User/Help-needed-to-do-a-loop-array-to-create-a-new-table/m-p/537936#M6847 <P>Hi,</P><P> </P><P>I'm writting a loop to create a new table as following and got stacked. </P><P>Here is an example table with a table of scores for different months. There are date_1 and Date_2(table #1).</P><P>I need to create a new table(table #2) to only keep the scores from date_1 to date_2, and in line with date_2(as Mon_0).</P><P>Is there a way to write a do loop/array to solve the problem? Really appreciate.</P><P> </P><P>Table #1</P><TABLE><TBODY><TR><TD>ID</TD><TD>date_1</TD><TD>date_2</TD><TD>sc_201501</TD><TD>sc_201502</TD><TD>sc_201503</TD><TD>sc_201504</TD><TD>sc_201505</TD><TD>sc_201506</TD><TD>sc_201507</TD><TD>sc_201508</TD><TD>sc_201509</TD><TD>sc_201510</TD><TD>sc_201511</TD><TD>sc_201512</TD><TD>sc_201601</TD><TD>sc_201602</TD><TD>sc_201603</TD><TD>sc_201604</TD><TD>sc_201605</TD><TD>sc_201606</TD><TD>sc_201607</TD><TD>sc_201608</TD><TD>sc_201609</TD><TD>sc_201610</TD><TD>sc_201611</TD><TD>sc_201612</TD></TR><TR><TD>1</TD><TD>2015_01</TD><TD>2016_02</TD><TD>0.658</TD><TD>0.888</TD><TD>0.330</TD><TD>0.938</TD><TD>0.148</TD><TD>0.062</TD><TD>0.598</TD><TD>0.944</TD><TD>0.770</TD><TD>0.189</TD><TD>0.144</TD><TD>0.842</TD><TD>0.372</TD><TD>0.481</TD><TD>0.123</TD><TD>0.630</TD><TD>0.276</TD><TD>0.292</TD><TD>0.626</TD><TD>0.665</TD><TD>0.885</TD><TD>0.614</TD><TD>0.331</TD><TD>0.467</TD></TR><TR><TD>2</TD><TD>2016_01</TD><TD>2016_12</TD><TD>0.414</TD><TD>0.915</TD><TD>0.748</TD><TD>0.160</TD><TD>0.388</TD><TD>0.100</TD><TD>0.751</TD><TD>0.089</TD><TD>0.786</TD><TD>0.126</TD><TD>0.305</TD><TD>0.852</TD><TD>0.874</TD><TD>0.875</TD><TD>0.414</TD><TD>0.404</TD><TD>0.268</TD><TD>0.730</TD><TD>0.760</TD><TD>0.443</TD><TD>0.577</TD><TD>0.231</TD><TD>0.641</TD><TD>0.398</TD></TR><TR><TD>3</TD><TD>2016_04</TD><TD>2016_12</TD><TD>0.614</TD><TD>0.166</TD><TD>0.465</TD><TD>0.347</TD><TD>0.680</TD><TD>0.042</TD><TD>0.400</TD><TD>0.959</TD><TD>0.223</TD><TD>0.603</TD><TD>0.918</TD><TD>0.954</TD><TD>0.289</TD><TD>0.480</TD><TD>0.626</TD><TD>0.638</TD><TD>0.451</TD><TD>0.478</TD><TD>0.197</TD><TD>0.175</TD><TD>0.093</TD><TD>0.096</TD><TD>0.535</TD><TD>0.990</TD></TR><TR><TD>4</TD><TD>2016_03</TD><TD>2016_11</TD><TD>0.054</TD><TD>0.871</TD><TD>0.087</TD><TD>0.286</TD><TD>0.801</TD><TD>0.701</TD><TD>0.617</TD><TD>0.015</TD><TD>0.215</TD><TD>0.546</TD><TD>0.467</TD><TD>0.219</TD><TD>0.561</TD><TD>0.397</TD><TD>0.878</TD><TD>0.481</TD><TD>0.418</TD><TD>0.887</TD><TD>0.758</TD><TD>0.575</TD><TD>0.750</TD><TD>0.364</TD><TD>0.898</TD><TD>0.437</TD></TR><TR><TD>5</TD><TD>2015_12</TD><TD>2016_05</TD><TD>0.888</TD><TD>0.453</TD><TD>0.649</TD><TD>0.617</TD><TD>0.734</TD><TD>0.244</TD><TD>0.512</TD><TD>0.033</TD><TD>0.964</TD><TD>0.416</TD><TD>0.739</TD><TD>0.183</TD><TD>0.665</TD><TD>0.588</TD><TD>0.962</TD><TD>0.755</TD><TD>0.500</TD><TD>0.236</TD><TD>0.983</TD><TD>0.843</TD><TD>0.256</TD><TD>0.489</TD><TD>0.394</TD><TD>0.682</TD></TR><TR><TD>6</TD><TD>2015_10</TD><TD>2016_03</TD><TD>0.517</TD><TD>0.493</TD><TD>0.239</TD><TD>0.960</TD><TD>0.050</TD><TD>0.082</TD><TD>0.701</TD><TD>0.576</TD><TD>0.892</TD><TD>0.446</TD><TD>0.167</TD><TD>0.467</TD><TD>0.085</TD><TD>0.047</TD><TD>0.492</TD><TD>0.837</TD><TD>0.289</TD><TD>0.107</TD><TD>0.840</TD><TD>0.442</TD><TD>0.951</TD><TD>0.785</TD><TD>0.169</TD><TD>0.173</TD></TR><TR><TD>7</TD><TD>2015_02</TD><TD>2015_12</TD><TD>0.206</TD><TD>0.587</TD><TD>0.518</TD><TD>0.233</TD><TD>0.906</TD><TD>0.983</TD><TD>0.273</TD><TD>0.845</TD><TD>0.600</TD><TD>0.514</TD><TD>0.920</TD><TD>0.182</TD><TD>0.433</TD><TD>0.026</TD><TD>0.948</TD><TD>0.281</TD><TD>0.347</TD><TD>0.272</TD><TD>0.818</TD><TD>0.054</TD><TD>0.415</TD><TD>0.759</TD><TD>0.547</TD><TD>0.319</TD></TR></TBODY></TABLE><P> </P><P>Table #2</P><TABLE><TBODY><TR><TD>ID</TD><TD>date_1</TD><TD>date_2</TD><TD>Mon_before14</TD><TD>Mon_before13</TD><TD>Mon_before12</TD><TD>Mon_before11</TD><TD>Mon_before10</TD><TD>Mon_before9</TD><TD>Mon_before8</TD><TD>Mon_before7</TD><TD>Mon_before6</TD><TD>Mon_before5</TD><TD>Mon_before4</TD><TD>Mon_before3</TD><TD>Mon_before2</TD><TD>Mon_before1</TD><TD>Mon_0</TD></TR><TR><TD>1</TD><TD>2015_01</TD><TD>2016_02</TD><TD> </TD><TD>0.658</TD><TD>0.888</TD><TD>0.330</TD><TD>0.938</TD><TD>0.148</TD><TD>0.062</TD><TD>0.598</TD><TD>0.944</TD><TD>0.770</TD><TD>0.189</TD><TD>0.144</TD><TD>0.842</TD><TD>0.372</TD><TD>0.481</TD></TR><TR><TD>2</TD><TD>2016_01</TD><TD>2016_12</TD><TD> </TD><TD> </TD><TD> </TD><TD>0.874</TD><TD>0.875</TD><TD>0.414</TD><TD>0.404</TD><TD>0.268</TD><TD>0.730</TD><TD>0.760</TD><TD>0.443</TD><TD>0.577</TD><TD>0.231</TD><TD>0.641</TD><TD>0.398</TD></TR><TR><TD>3</TD><TD>2016_04</TD><TD>2016_12</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>0.638</TD><TD>0.451</TD><TD>0.478</TD><TD>0.197</TD><TD>0.175</TD><TD>0.093</TD><TD>0.096</TD><TD>0.535</TD><TD>0.990</TD></TR><TR><TD>4</TD><TD>2016_03</TD><TD>2016_11</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>0.878</TD><TD>0.481</TD><TD>0.418</TD><TD>0.887</TD><TD>0.758</TD><TD>0.575</TD><TD>0.750</TD><TD>0.364</TD><TD>0.898</TD></TR><TR><TD>5</TD><TD>2015_12</TD><TD>2016_05</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>0.183</TD><TD>0.665</TD><TD>0.588</TD><TD>0.962</TD><TD>0.755</TD><TD>0.500</TD></TR><TR><TD>6</TD><TD>2015_10</TD><TD>2016_03</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>0.446</TD><TD>0.167</TD><TD>0.467</TD><TD>0.085</TD><TD>0.047</TD><TD>0.492</TD></TR><TR><TD>7</TD><TD>2015_02</TD><TD>2015_12</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD>0.587</TD><TD>0.518</TD><TD>0.233</TD><TD>0.906</TD><TD>0.983</TD><TD>0.273</TD><TD>0.845</TD><TD>0.600</TD><TD>0.514</TD><TD>0.920</TD><TD>0.182</TD></TR></TBODY></TABLE><P> </P><P>Many thanks,</P><P>Olivia<BR /> </P> Sat, 23 Feb 2019 05:24:27 GMT https://communities.sas.com/t5/New-SAS-User/Help-needed-to-do-a-loop-array-to-create-a-new-table/m-p/537936#M6847 oliviameng 2019-02-23T05:24:27Z Re: Help needed to do a loop/array to create a new table https://communities.sas.com/t5/New-SAS-User/Help-needed-to-do-a-loop-array-to-create-a-new-table/m-p/537943#M6848 <P>Transpose to a long format, then set the "month before" variable with intck(), or delete if it's before date_1 or after date_2.</P> <P>If you still need the horizontal format in the output, transpose back.</P> Sat, 23 Feb 2019 07:39:24 GMT https://communities.sas.com/t5/New-SAS-User/Help-needed-to-do-a-loop-array-to-create-a-new-table/m-p/537943#M6848 Kurt_Bremser 2019-02-23T07:39:24Z Re: Help needed to do a loop/array to create a new table https://communities.sas.com/t5/New-SAS-User/Help-needed-to-do-a-loop-array-to-create-a-new-table/m-p/538743#M6986 <P>Thank you. This is much easier way than writing a loop and with arrays.</P><P> </P><P>Olivia</P> Tue, 26 Feb 2019 18:18:07 GMT https://communities.sas.com/t5/New-SAS-User/Help-needed-to-do-a-loop-array-to-create-a-new-table/m-p/538743#M6986 oliviameng 2019-02-26T18:18:07Z