topic Re: Drop all but shared variables in merge in SAS Studio https://communities.sas.com/t5/SAS-Studio/Drop-all-but-shared-variables-in-merge/m-p/450272#M5104 <P>perfect. Thanks!</P> Sun, 01 Apr 2018 21:35:18 GMT sastuck 2018-04-01T21:35:18Z Drop all but shared variables in merge https://communities.sas.com/t5/SAS-Studio/Drop-all-but-shared-variables-in-merge/m-p/450266#M5100 <P>Hello,&nbsp;</P><P>&nbsp;</P><P><a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/159">@Tom</a>&nbsp;wrote the following code so that I could see how my&nbsp;MERGE statement had more than one data set with repeats of BY values:</P><P>&nbsp;</P><PRE>*MERGE statement has more than one data set with repeats of BY values; proc sort data=paper.CSRP_annual_returns out=paper.CSRP_annual_returns2 nodupkey; by ticker; run; *Merge CEO data and firm data; DATA paper.ceo_firm ; length ticker $5 source $10; MERGE paper.CSRP_annual_returns2 (in=in1) paper.compustat_execucomp4 (in=in2) ; BY ticker; if in1 and in2 then source='BOTH'; else if in1 then source='ANNUAL'; else source='EXECCOMP'; RUN;</PRE><P>The output includes a variable that tells me which of the two data sets the observation is coming from. It also says which are coming from both. I would like to keep only those rows which have "BOTH" as a source. What is the best way to do this?&nbsp;</P><P>&nbsp;</P><P>Here is an example of what the current output looks like:</P><P>&nbsp;</P><DIV class=" dgrid-row dgrid-row-even ui-state-default dgrid-selected ui-state-active"><TABLE><TBODY><TR><TD>row</TD><TD>ticker</TD><TD>source</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>.</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>.</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD></TR></TBODY></TABLE></DIV><DIV class=" dgrid-row dgrid-row-odd ui-state-default"><TABLE><TBODY><TR><TD>408</TD><TD>ACO</TD><TD>BOTH</TD><TD>2010</TD><TD>9.0442006703</TD><TD>Lawrence E. Washow</TD><TD>&nbsp;</TD><TD>1</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>&nbsp;</TD><TD>7456</TD><TD>AMCOL INTERNATIONAL CORP</TD><TD>Chief Executive Officer, President, Director and Member of Executive Committee</TD><TD>CEO</TD><TD>670</TD><TD>0</TD><TD>0</TD><TD>413.76</TD><TD>136</TD><TD>666.628</TD><TD>3163.88</TD><TD>5050.268</TD><TD>670</TD><TD>5050.268</TD><TD>5547.336</TD><TD>1498.699</TD><TD>4190.768</TD><TD>211.988</TD><TD>0.676</TD></TR></TBODY></TABLE></DIV><DIV class=" dgrid-row dgrid-row-even ui-state-default"><TABLE><TBODY><TR><TD>409</TD><TD>ACO</TD><TD>BOTH</TD><TD>2011</TD><TD>9.0442006703</TD><TD>Ryan F. McKendrick</TD><TD>&nbsp;</TD><TD>1</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>&nbsp;</TD><TD>22282</TD><TD>AMCOL INTERNATIONAL CORP</TD><TD>Chief Executive Officer, President and Director</TD><TD>CEO</TD><TD>500</TD><TD>0</TD><TD>613.2</TD><TD>342.6</TD><TD>601.786</TD><TD>363.203</TD><TD>35.644</TD><TD>2456.433</TD><TD>500</TD><TD>1843.233</TD><TD>2609.998</TD><TD>1040.818</TD><TD>3000</TD><TD>201.949</TD><TD>0.632</TD></TR></TBODY></TABLE></DIV><DIV class=" dgrid-row dgrid-row-odd ui-state-default"><TABLE><TBODY><TR><TD>410</TD><TD>ACO</TD><TD>BOTH</TD><TD>2012</TD><TD>9.0442006703</TD><TD>Ryan F. McKendrick</TD><TD>&nbsp;</TD><TD>1</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>&nbsp;</TD><TD>22282</TD><TD>AMCOL INTERNATIONAL CORP</TD><TD>Chief Executive Officer, President and Director</TD><TD>CEO</TD><TD>525</TD><TD>0</TD><TD>0</TD><TD>370.8</TD><TD>496</TD><TD>653.54</TD><TD>36.011</TD><TD>2081.351</TD><TD>525</TD><TD>2081.351</TD><TD>1909.228</TD><TD>1090.818</TD><TD>3150</TD><TD>90.357</TD><TD>0.279</TD></TR></TBODY></TABLE></DIV><DIV class=" dgrid-row dgrid-row-even ui-state-default"><TABLE><TBODY><TR><TD>411</TD><TD>ACO</TD><TD>BOTH</TD><TD>2013</TD><TD>9.0442006703</TD><TD>Ryan F. McKendrick</TD><TD>&nbsp;</TD><TD>1</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>&nbsp;</TD><TD>22282</TD><TD>AMCOL INTERNATIONAL CORP</TD><TD>Chief Executive Officer, President, Director and Member of Executive Committee</TD><TD>CEO</TD><TD>700</TD><TD>517</TD><TD>833.472</TD><TD>508.375</TD><TD>0</TD><TD>420.204</TD><TD>37.615</TD><TD>3016.666</TD><TD>1217</TD><TD>3016.666</TD><TD>1877.932</TD><TD>1436.183</TD><TD>4200</TD><TD>75.084</TD><TD>0.231</TD></TR></TBODY></TABLE></DIV><DIV class=" dgrid-row dgrid-row-odd ui-state-default"><TABLE><TBODY><TR><TD>412</TD><TD>ACOM</TD><TD>ANNUAL</TD><TD>2010</TD><TD>88.344227811</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>.</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>.</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD></TR></TBODY></TABLE></DIV><DIV class=" dgrid-row dgrid-row-even ui-state-default"><TABLE><TBODY><TR><TD>413</TD><TD>ACOR</TD><TD>BOTH</TD><TD>2010</TD><TD>7.2911181633</TD><TD>Ron Cohen, M.D.</TD><TD>&nbsp;</TD><TD>1</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>&nbsp;</TD><TD>46385</TD><TD>ACORDA THERAPEUTICS INC</TD><TD>Founder, Chairman, Chief Executive Officer and President</TD><TD>CEO</TD><TD>589.75</TD><TD>592.86</TD><TD>3817.344</TD><TD>4549.547</TD><TD>0</TD><TD>0</TD><TD>7.35</TD><TD>9556.851</TD><TD>1182.61</TD><TD>9557.298</TD><TD>5485.461</TD><TD>9276.502</TD><TD>9276.502</TD><TD>1149.837</TD><TD>2.941</TD></TR></TBODY></TABLE></DIV><DIV class=" dgrid-row dgrid-row-odd ui-state-default"><TABLE><TBODY><TR><TD>414</TD><TD>ACOR</TD><TD>BOTH</TD><TD>2011</TD><TD>7.2911181633</TD><TD>Ron Cohen, M.D.</TD><TD>&nbsp;</TD><TD>1</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>&nbsp;</TD><TD>46385</TD><TD>ACORDA THERAPEUTICS INC</TD><TD>Founder, Chief Executive Officer, President and Director</TD><TD>CEO</TD><TD>635</TD><TD>0</TD><TD>965.125</TD><TD>1605.196</TD><TD>557.92</TD><TD>0</TD><TD>7.35</TD><TD>3770.591</TD><TD>635</TD><TD>3770.583</TD><TD>5966.926</TD><TD>9085.989</TD><TD>9911.129</TD><TD>1164.359</TD><TD>2.904</TD></TR></TBODY></TABLE></DIV><DIV class=" dgrid-row dgrid-row-even ui-state-default"><TABLE><TBODY><TR><TD>415</TD><TD>ACOR</TD><TD>BOTH</TD><TD>2012</TD><TD>7.2911181633</TD><TD>Ron Cohen, M.D.</TD><TD>&nbsp;</TD><TD>1</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>&nbsp;</TD><TD>46385</TD><TD>ACORDA THERAPEUTICS INC</TD><TD>Founder, Chief Executive Officer, President and Director</TD><TD>CEO</TD><TD>690</TD><TD>0</TD><TD>988.125</TD><TD>1590.684</TD><TD>571.2</TD><TD>0</TD><TD>7.35</TD><TD>3847.359</TD><TD>690</TD><TD>3847.425</TD><TD>2598.872</TD><TD>8466.185</TD><TD>9028.265</TD><TD>1309.41</TD><TD>3.251</TD></TR></TBODY></TABLE></DIV><DIV class=" dgrid-row dgrid-row-odd ui-state-default"><TABLE><TBODY><TR><TD>416</TD><TD>ACOR</TD><TD>BOTH</TD><TD>2013</TD><TD>7.2911181633</TD><TD>Ron Cohen, M.D.</TD><TD>&nbsp;</TD><TD>1</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>&nbsp;</TD><TD>46385</TD><TD>ACORDA THERAPEUTICS INC</TD><TD>Founder, Chief Executive Officer, President and Director</TD><TD>CEO</TD><TD>720.833</TD><TD>0</TD><TD>685.35</TD><TD>1976.389</TD><TD>527.8</TD><TD>0</TD><TD>7.65</TD><TD>3918.022</TD><TD>720.833</TD><TD>3918.022</TD><TD>12601.719</TD><TD>7101.215</TD><TD>7681.215</TD><TD>1300.266</TD><TD>3.115</TD></TR></TBODY></TABLE></DIV><DIV class=" dgrid-row dgrid-row-even ui-state-default"><TABLE><TBODY><TR><TD>417</TD><TD>ACOR</TD><TD>BOTH</TD><TD>2014</TD><TD>7.2911181633</TD><TD>Ron Cohen, M.D.</TD><TD>&nbsp;</TD><TD>1</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>&nbsp;</TD><TD>46385</TD><TD>ACORDA THERAPEUTICS INC</TD><TD>Founder, Chief Executive Officer, President and Director</TD><TD>CEO</TD><TD>745.833</TD><TD>0</TD><TD>700.413</TD><TD>1939.685</TD><TD>651.6</TD><TD>0</TD><TD>7.65</TD><TD>4045.181</TD><TD>745.833</TD><TD>4045.181</TD><TD>3887.288</TD><TD>6710.938</TD><TD>7310.938</TD><TD>1389.888</TD><TD>3.244</TD></TR></TBODY></TABLE></DIV><DIV class=" dgrid-row dgrid-row-odd ui-state-default"><TABLE><TBODY><TR><TD>418</TD><TD>ACOR</TD><TD>BOTH</TD><TD>2015</TD><TD>7.2911181633</TD><TD>Ron Cohen, M.D.</TD><TD>&nbsp;</TD><TD>1</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>&nbsp;</TD><TD>46385</TD><TD>ACORDA THERAPEUTICS INC</TD><TD>Founder, Chief Executive Officer, President and Director</TD><TD>CEO</TD><TD>768.75</TD><TD>0</TD><TD>607.58</TD><TD>1510.794</TD><TD>553.728</TD><TD>0</TD><TD>7.95</TD><TD>3448.802</TD><TD>768.75</TD><TD>3448.802</TD><TD>4685.349</TD><TD>12235.274</TD><TD>12853.274</TD><TD>1236.761</TD><TD>2.68</TD></TR></TBODY></TABLE></DIV><DIV class=" dgrid-row dgrid-row-even ui-state-default"><TABLE><TBODY><TR><TD>419</TD><TD>ACOR</TD><TD>BOTH</TD><TD>2016</TD><TD>7.2911181633</TD><TD>Ron Cohen, M.D.</TD><TD>&nbsp;</TD><TD>1</TD><TD>0</TD><TD>0</TD><TD>0</TD><TD>&nbsp;</TD><TD>46385</TD><TD>ACORDA THERAPEUTICS INC</TD><TD>Founder, Chief Executive Officer, President and Director</TD><TD>CEO</TD><TD>772.5</TD><TD>0</TD><TD>1046.501</TD><TD>1043.884</TD><TD>562.38</TD><TD>0</TD><TD>7.95</TD><TD>3433.215</TD><TD>772.5</TD><TD>3433.215</TD><TD>2660.472</TD><TD>3136.16</TD><TD>3818.432</TD><TD>1422.28</TD><TD>3.065</TD></TR></TBODY></TABLE></DIV><DIV class=" dgrid-row dgrid-row-odd ui-state-default"><TABLE><TBODY><TR><TD>420</TD><TD>ACPW</TD><TD>ANNUAL</TD><TD>2010</TD><TD>94.858409388</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>.</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>.</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD><TD>.</TD></TR></TBODY></TABLE></DIV><DIV class=" dgrid-row dgrid-row-even ui-state-default"><TABLE><TBODY><TR><TD>421</TD><TD>ACRE</TD><TD>ANNUAL</TD><TD>2012</TD><TD>0.7119757952</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>And here is the log:</P><P>&nbsp;</P><DIV class="sasSource">1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;</DIV><DIV class="sasSource">70</DIV><DIV class="sasSource">71 *MERGE statement has more than one data set with repeats of BY values;</DIV><DIV class="sasSource">72 proc sort data=paper.CSRP_annual_returns</DIV><DIV class="sasSource">73 out=paper.CSRP_annual_returns2</DIV><DIV class="sasSource">74 nodupkey;</DIV><DIV class="sasSource">75 by ticker;</DIV><DIV class="sasSource">76 run;</DIV><DIV class="sasNote">&nbsp;</DIV><DIV class="sasNote">NOTE: There were 53176 observations read from the data set PAPER.CSRP_ANNUAL_RETURNS.</DIV><DIV class="sasNote">NOTE: 42661 observations with duplicate key values were deleted.</DIV><DIV class="sasNote">NOTE: The data set PAPER.CSRP_ANNUAL_RETURNS2 has 10515 observations and 3 variables.</DIV><DIV class="sasNote">NOTE: PROCEDURE SORT used (Total process time):</DIV><DIV class="sasNote">real time 0.03 seconds</DIV><DIV class="sasNote">user cpu time 0.01 seconds</DIV><DIV class="sasNote">system cpu time 0.01 seconds</DIV><DIV class="sasNote">memory 6126.06k</DIV><DIV class="sasNote">OS Memory 36452.00k</DIV><DIV class="sasNote">Timestamp 04/01/2018 07:47:46 PM</DIV><DIV class="sasNote">Step Count 61 Switch Count 2</DIV><DIV class="sasNote">Page Faults 0</DIV><DIV class="sasNote">Page Reclaims 876</DIV><DIV class="sasNote">Page Swaps 0</DIV><DIV class="sasNote">Voluntary Context Switches 46</DIV><DIV class="sasNote">Involuntary Context Switches 0</DIV><DIV class="sasNote">Block Input Operations 0</DIV><DIV class="sasNote">Block Output Operations 528</DIV><DIV class="sasNote">&nbsp;</DIV><DIV class="sasNote">&nbsp;</DIV><DIV class="sasSource">77</DIV><DIV class="sasSource">78 *Merge CEO data and firm data;</DIV><DIV class="sasSource">79 DATA paper.ceo_firm ;</DIV><DIV class="sasSource">80 length ticker $5 source $10;</DIV><DIV class="sasSource">81 MERGE</DIV><DIV class="sasSource">82 paper.CSRP_annual_returns2 (in=in1)</DIV><DIV class="sasSource">83 paper.compustat_execucomp4 (in=in2)</DIV><DIV class="sasSource">84 ;</DIV><DIV class="sasSource">85 BY ticker;</DIV><DIV class="sasSource">86 if in1 and in2 then source='BOTH';</DIV><DIV class="sasSource">87 else if in1 then source='ANNUAL';</DIV><DIV class="sasSource">88 else source='EXECCOMP';</DIV><DIV class="sasSource">89 RUN;</DIV><DIV class="sasNote">&nbsp;</DIV><DIV class="sasNote">NOTE: There were 10515 observations read from the data set PAPER.CSRP_ANNUAL_RETURNS2.</DIV><DIV class="sasNote">NOTE: There were 13346 observations read from the data set PAPER.COMPUSTAT_EXECUCOMP4.</DIV><DIV class="sasNote">NOTE: The data set PAPER.CEO_FIRM has 21807 observations and 109 variables.</DIV><DIV class="sasNote">NOTE: DATA statement used (Total process time):</DIV><DIV class="sasNote">real time 0.17 seconds</DIV><DIV class="sasNote">user cpu time 0.02 seconds</DIV><DIV class="sasNote">system cpu time 0.05 seconds</DIV><DIV class="sasNote">memory 4222.00k</DIV><DIV class="sasNote">OS Memory 35504.00k</DIV><DIV class="sasNote">Timestamp 04/01/2018 07:47:47 PM</DIV><DIV class="sasNote">Step Count 62 Switch Count 2</DIV><DIV class="sasNote">Page Faults 0</DIV><DIV class="sasNote">Page Reclaims 738</DIV><DIV class="sasNote">Page Swaps 0</DIV><DIV class="sasNote">Voluntary Context Switches 1472</DIV><DIV class="sasNote">Involuntary Context Switches 1</DIV><DIV class="sasNote">Block Input Operations 544</DIV><DIV class="sasNote">Block Output Operations 88840</DIV><DIV class="sasNote">&nbsp;</DIV><DIV class="sasNote">&nbsp;</DIV><DIV class="sasSource">90</DIV><DIV class="sasSource">91 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;</DIV><DIV class="sasSource">103</DIV><DIV class="sasSource">&nbsp;</DIV><DIV class="sasSource">&nbsp;</DIV><DIV class="sasSource">Thanks for the help!</DIV></DIV> Sun, 01 Apr 2018 19:54:22 GMT https://communities.sas.com/t5/SAS-Studio/Drop-all-but-shared-variables-in-merge/m-p/450266#M5100 sastuck 2018-04-01T19:54:22Z Re: Drop all but shared variables in merge https://communities.sas.com/t5/SAS-Studio/Drop-all-but-shared-variables-in-merge/m-p/450270#M5102 <P>You can do that by modifying&nbsp;<a href="https://communities.sas.com/t5/user/viewprofilepage/user-id/159">@Tom</a>'s code:</P> <PRE>*Merge CEO data and firm data; DATA paper.ceo_firm ; length ticker $5 source $10; MERGE paper.CSRP_annual_returns2 (in=in1) paper.compustat_execucomp4 (in=in2) ; BY ticker; if in1 and in2; RUN; </PRE> <P>Art, CEO, AnalystFinder.com</P> <P>&nbsp;</P> <P>&nbsp;</P> Sun, 01 Apr 2018 21:32:48 GMT https://communities.sas.com/t5/SAS-Studio/Drop-all-but-shared-variables-in-merge/m-p/450270#M5102 art297 2018-04-01T21:32:48Z Re: Drop all but shared variables in merge https://communities.sas.com/t5/SAS-Studio/Drop-all-but-shared-variables-in-merge/m-p/450272#M5104 <P>perfect. Thanks!</P> Sun, 01 Apr 2018 21:35:18 GMT https://communities.sas.com/t5/SAS-Studio/Drop-all-but-shared-variables-in-merge/m-p/450272#M5104 sastuck 2018-04-01T21:35:18Z