topic Re: Merging 2 datasets in SAS Programming https://communities.sas.com/t5/SAS-Programming/Merging-2-datasets/m-p/492863#M129612 <P>Can you explain the logic for the Column calculation? Not quite following that.</P> <P>&nbsp;</P> <PRE><CODE class=" language-sas">data want; set have; retain new_plate_number 0; counter=_n_; if plate in (1,2) then row='A'; else if plate in (3,4) then row='B'; if mod(_n_, 320)=1 then new_plate_number + 1; run;</CODE></PRE> <P>&nbsp;</P> <P>&nbsp;</P> Thu, 06 Sep 2018 02:26:29 GMT Reeza 2018-09-06T02:26:29Z Merging 2 datasets https://communities.sas.com/t5/SAS-Programming/Merging-2-datasets/m-p/492857#M129609 <P>Hello all,</P><P>&nbsp;</P><P>I am an occasional dabbler of SAS and I am currently stuck on trying to find a solution to my problem.</P><P>&nbsp;</P><P>I have&nbsp;a rather&nbsp;large&nbsp;excel sheet with many variables which I managed to import into SAS. For convenience sake, I am going to say that I have a dataset with 640 observations and 2 variables. The two variables are plate and well. I want to create a new dataset where I am combining every 4 plates into one plate and I want to add 2 new variables row and column.&nbsp;</P><P>Part of my Dataset :</P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Plate</TD><TD>Well</TD></TR><TR><TD>1</TD><TD>A02</TD></TR><TR><TD>2</TD><TD>A02</TD></TR><TR><TD>3</TD><TD>A02</TD></TR><TR><TD>4</TD><TD>A02</TD></TR><TR><TD>1</TD><TD>A03</TD></TR><TR><TD>2</TD><TD>A03</TD></TR><TR><TD>3</TD><TD>A03</TD></TR><TR><TD>4</TD><TD>A03</TD></TR></TBODY></TABLE><P><BR />portion of Dataset i need:</P><TABLE border="0" cellspacing="0" cellpadding="0"><TBODY><TR><TD>Plate</TD><TD>Well</TD><TD>Row</TD><TD>Column</TD><TD>new plate no</TD></TR><TR><TD>1</TD><TD>A02</TD><TD>A</TD><TD>3</TD><TD>1</TD></TR><TR><TD>2</TD><TD>A02</TD><TD>A</TD><TD>4</TD><TD>1</TD></TR><TR><TD>3</TD><TD>A02</TD><TD>B</TD><TD>3</TD><TD>1</TD></TR><TR><TD>4</TD><TD>A02</TD><TD>B</TD><TD>4</TD><TD>1</TD></TR><TR><TD>1</TD><TD>A03</TD><TD>A</TD><TD>5</TD><TD>1</TD></TR><TR><TD>2</TD><TD>A03</TD><TD>A</TD><TD>6</TD><TD>1</TD></TR><TR><TD>3</TD><TD>A03</TD><TD>B</TD><TD>5</TD><TD>1</TD></TR><TR><TD>4</TD><TD>A03</TD><TD>B</TD><TD>6</TD><TD>1</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>Every plate in the original dataset has 80&nbsp;observations&nbsp;and I want to combine 4x80 into 1 plate with 320 values, combine the next 320 values into a second plate and so on.&nbsp;</P><P>&nbsp;</P><P>My original plan was to create a second dataset with the additional 3 observations and do&nbsp;a one to one merge, save it, delete those observations from original dataset and repeat but I have too many observations to&nbsp;do it this way&nbsp;and my method is also very error prone. I am sure that there is a better way of doing this, I am just not able to figure it out.</P><P>&nbsp;</P><P>I have attached an excel sheet which has 80 observations to better understand the pattern that I am trying to create.Would someone be able to help me out with this? Please let me know if I need to provide more information/make my question clearer. Thank you so much.</P> Thu, 06 Sep 2018 01:30:09 GMT https://communities.sas.com/t5/SAS-Programming/Merging-2-datasets/m-p/492857#M129609 vseshad 2018-09-06T01:30:09Z Re: Merging 2 datasets https://communities.sas.com/t5/SAS-Programming/Merging-2-datasets/m-p/492863#M129612 <P>Can you explain the logic for the Column calculation? Not quite following that.</P> <P>&nbsp;</P> <PRE><CODE class=" language-sas">data want; set have; retain new_plate_number 0; counter=_n_; if plate in (1,2) then row='A'; else if plate in (3,4) then row='B'; if mod(_n_, 320)=1 then new_plate_number + 1; run;</CODE></PRE> <P>&nbsp;</P> <P>&nbsp;</P> Thu, 06 Sep 2018 02:26:29 GMT https://communities.sas.com/t5/SAS-Programming/Merging-2-datasets/m-p/492863#M129612 Reeza 2018-09-06T02:26:29Z Re: Merging 2 datasets https://communities.sas.com/t5/SAS-Programming/Merging-2-datasets/m-p/492864#M129613 <P>Just looking at what you posted (it's dangerous to open posted spreadsheets), here's an approach:</P> <P>&nbsp;</P> <P>data want;</P> <P>set have;</P> <P>if mod(_n_, 80) = 1 then do;</P> <P>&nbsp; &nbsp;new_plate + 1;</P> <P>&nbsp; &nbsp;well_count = 2;</P> <P>end;</P> <P>else if mod(_n_, 4) = 1 then well_count + 2;</P> <P>if mod(_n_, 2)&nbsp; in (1, 3) then column = well_count + 1;</P> <P>else column = well_count + 2;</P> <P>retain row 'A';</P> <P>back1 = lag(row);</P> <P>back2 = lag2(row);</P> <P>if back1 = back2 = 'A' then row='B';</P> <P>else if back1=back2='B' then row='A';</P> <P>drop well_count;</P> <P>run;</P> <P>&nbsp;</P> <P>It's untested code, and done without seeing the spreadsheet, so may need a little tweaking.&nbsp; It assumes there are always four observations for a well.&nbsp;&nbsp;</P> Thu, 06 Sep 2018 02:29:18 GMT https://communities.sas.com/t5/SAS-Programming/Merging-2-datasets/m-p/492864#M129613 Astounding 2018-09-06T02:29:18Z Re: Merging 2 datasets https://communities.sas.com/t5/SAS-Programming/Merging-2-datasets/m-p/492867#M129616 <P>Assuming col is just alternating numbers offset by 2 for some reason, this works on your sample data at least:</P> <P>&nbsp;</P> <PRE><CODE class=" language-sas">*create fake data; data have; input Plate Well $3.; cards; 1 A02 2 A02 3 A02 4 A02 1 A03 2 A03 3 A03 4 A03 ; run; *ensure sort is correct; proc sort data=have; by well plate; run; data want; set have; by well; *set initial variables and keep them until explicitly changed; retain new_plate_number 0 start_col 0; *plate calculation; if plate in (1,2) then row='A'; else if plate in (3,4) then row='B'; *plate number; if mod(_n_, 320)=1 then new_plate_number + 1; *column calculation; if first.well then start_col+2; if plate in (1,3) then col=start_col+1; else if plate in (2,4) then col=start_col+2; drop start_col; run;</CODE></PRE> Thu, 06 Sep 2018 02:37:42 GMT https://communities.sas.com/t5/SAS-Programming/Merging-2-datasets/m-p/492867#M129616 Reeza 2018-09-06T02:37:42Z Re: Merging 2 datasets https://communities.sas.com/t5/SAS-Programming/Merging-2-datasets/m-p/493005#M129672 <P>I'm sorry I wasn't very clear.</P><P>&nbsp;</P><P>What I am trying to do with the column calculation is trying to arrange the first value of the 4 plates into a square. Hence A01 of plate 1 becomes A01 of newplate1, A01 of plate 2 becomes A02 of newplate1,&nbsp;<SPAN>A01 of plate3 becomes B01 of newplate 1, and&nbsp;A01 of plate4&nbsp;&nbsp;</SPAN>becomes B02 of newplate1.&nbsp;</P><P>&nbsp;</P><P>The new plate should look something like this. Every row is labeled A-P and every column on the new plate is labeled 1-24. Please let me know if this is helpful. Thanks!</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>1A1</TD><TD>2A1</TD><TD>1A2</TD><TD>2A2</TD><TD>1A3</TD><TD>2A3</TD><TD>1A4</TD><TD>2A4</TD><TD>1A5</TD><TD>2A5</TD><TD>1A6</TD><TD>2A6</TD><TD>1A7</TD><TD>2A7</TD><TD>1A8</TD><TD>2A8</TD><TD>1A9</TD><TD>2A9</TD><TD>1A10</TD><TD>2A10</TD><TD>1A11</TD><TD>2A11</TD><TD>1A12</TD><TD>2A12</TD></TR><TR><TD>3A1</TD><TD>4A1</TD><TD>3A2</TD><TD>4A2</TD><TD>3A3</TD><TD>4A3</TD><TD>3A4</TD><TD>4A4</TD><TD>3A5</TD><TD>4A5</TD><TD>3A6</TD><TD>4A6</TD><TD>3A7</TD><TD>4A7</TD><TD>3A8</TD><TD>4A8</TD><TD>3A9</TD><TD>4A9</TD><TD>3A10</TD><TD>4A10</TD><TD>3A11</TD><TD>4A11</TD><TD>3A12</TD><TD>4A12</TD></TR><TR><TD>1B1</TD><TD>2B1</TD><TD>1B2</TD><TD>2B2</TD><TD>1B3</TD><TD>2B3</TD><TD>1B4</TD><TD>2B4</TD><TD>1B5</TD><TD>2B5</TD><TD>1B6</TD><TD>2B6</TD><TD>1B7</TD><TD>2B7</TD><TD>1B8</TD><TD>2B8</TD><TD>1B9</TD><TD>2B9</TD><TD>1B10</TD><TD>2B10</TD><TD>1B11</TD><TD>2B11</TD><TD>1B12</TD><TD>2B12</TD></TR><TR><TD>3B1</TD><TD>4B1</TD><TD>3B2</TD><TD>4B2</TD><TD>3B3</TD><TD>4B3</TD><TD>3B4</TD><TD>4B4</TD><TD>3B5</TD><TD>4B5</TD><TD>3B6</TD><TD>4B6</TD><TD>3B7</TD><TD>4B7</TD><TD>3B8</TD><TD>4B8</TD><TD>3B9</TD><TD>4B9</TD><TD>3B10</TD><TD>4B10</TD><TD>3B11</TD><TD>4B11</TD><TD>3B12</TD><TD>4B12</TD></TR><TR><TD>1C1</TD><TD>2C1</TD><TD>1C2</TD><TD>2C2</TD><TD>1C3</TD><TD>2C3</TD><TD>1C4</TD><TD>2C4</TD><TD>1C5</TD><TD>2C5</TD><TD>1C6</TD><TD>2C6</TD><TD>1C7</TD><TD>2C7</TD><TD>1C8</TD><TD>2C8</TD><TD>1C9</TD><TD>2C9</TD><TD>1C10</TD><TD>2C10</TD><TD>1C11</TD><TD>2C11</TD><TD>1C12</TD><TD>2C12</TD></TR><TR><TD>3C1</TD><TD>4C1</TD><TD>3C2</TD><TD>4C2</TD><TD>3C3</TD><TD>4C3</TD><TD>3C4</TD><TD>4C4</TD><TD>3C5</TD><TD>4C5</TD><TD>3C6</TD><TD>4C6</TD><TD>3C7</TD><TD>4C7</TD><TD>3C8</TD><TD>4C8</TD><TD>3C9</TD><TD>4C9</TD><TD>3C10</TD><TD>4C10</TD><TD>3C11</TD><TD>4C11</TD><TD>3C12</TD><TD>4C12</TD></TR><TR><TD>1D1</TD><TD>2D1</TD><TD>1D2</TD><TD>2D2</TD><TD>1D3</TD><TD>2D3</TD><TD>1D4</TD><TD>2D4</TD><TD>1D5</TD><TD>2D5</TD><TD>1D6</TD><TD>2D6</TD><TD>1D7</TD><TD>2D7</TD><TD>1D8</TD><TD>2D8</TD><TD>1D9</TD><TD>2D9</TD><TD>1D10</TD><TD>2D10</TD><TD>1D11</TD><TD>2D11</TD><TD>1D12</TD><TD>2D12</TD></TR><TR><TD>3D1</TD><TD>4D1</TD><TD>3D2</TD><TD>4D2</TD><TD>3D3</TD><TD>4D3</TD><TD>3D4</TD><TD>4D4</TD><TD>3D5</TD><TD>4D5</TD><TD>3D6</TD><TD>4D6</TD><TD>3D7</TD><TD>4D7</TD><TD>3D8</TD><TD>4D8</TD><TD>3D9</TD><TD>4D9</TD><TD>3D10</TD><TD>4D10</TD><TD>3D11</TD><TD>4D11</TD><TD>3D12</TD><TD>4D12</TD></TR><TR><TD>1E1</TD><TD>2E1</TD><TD>1E2</TD><TD>2E2</TD><TD>1E3</TD><TD>2E3</TD><TD>1E4</TD><TD>2E4</TD><TD>1E5</TD><TD>2E5</TD><TD>1E6</TD><TD>2E6</TD><TD>1E7</TD><TD>2E7</TD><TD>1E8</TD><TD>2E8</TD><TD>1E9</TD><TD>2E9</TD><TD>1E10</TD><TD>2E10</TD><TD>1E11</TD><TD>2E11</TD><TD>1E12</TD><TD>2E12</TD></TR><TR><TD>3E1</TD><TD>4E1</TD><TD>3E2</TD><TD>4E2</TD><TD>3E3</TD><TD>4E3</TD><TD>3E4</TD><TD>4E4</TD><TD>3E5</TD><TD>4E5</TD><TD>3E6</TD><TD>4E6</TD><TD>3E7</TD><TD>4E7</TD><TD>3E8</TD><TD>4E8</TD><TD>3E9</TD><TD>4E9</TD><TD>3E10</TD><TD>4E10</TD><TD>3E11</TD><TD>4E11</TD><TD>3E12</TD><TD>4E12</TD></TR><TR><TD>1F1</TD><TD>2F1</TD><TD>1F2</TD><TD>2F2</TD><TD>1F3</TD><TD>2F3</TD><TD>1F4</TD><TD>2F4</TD><TD>1F5</TD><TD>2F5</TD><TD>1F6</TD><TD>2F6</TD><TD>1F7</TD><TD>2F7</TD><TD>1F8</TD><TD>2F8</TD><TD>1F9</TD><TD>2F9</TD><TD>1F10</TD><TD>2F10</TD><TD>1F11</TD><TD>2F11</TD><TD>1F12</TD><TD>2F12</TD></TR><TR><TD>3F1</TD><TD>4F1</TD><TD>3F2</TD><TD>4F2</TD><TD>3F3</TD><TD>4F3</TD><TD>3F4</TD><TD>4F4</TD><TD>3F5</TD><TD>4F5</TD><TD>3F6</TD><TD>4F6</TD><TD>3F7</TD><TD>4F7</TD><TD>3F8</TD><TD>4F8</TD><TD>3F9</TD><TD>4F9</TD><TD>3F10</TD><TD>4F10</TD><TD>3F11</TD><TD>4F11</TD><TD>3F12</TD><TD>4F12</TD></TR><TR><TD>1G1</TD><TD>2G1</TD><TD>1G2</TD><TD>2G2</TD><TD>1G3</TD><TD>2G3</TD><TD>1G4</TD><TD>2G4</TD><TD>1G5</TD><TD>2G5</TD><TD>1G6</TD><TD>2G6</TD><TD>1G7</TD><TD>2G7</TD><TD>1G8</TD><TD>2G8</TD><TD>1G9</TD><TD>2G9</TD><TD>1G10</TD><TD>2G10</TD><TD>1G11</TD><TD>2G11</TD><TD>1G12</TD><TD>2G12</TD></TR><TR><TD>3G1</TD><TD>4G1</TD><TD>3G2</TD><TD>4G2</TD><TD>3G3</TD><TD>4G3</TD><TD>3G4</TD><TD>4G4</TD><TD>3G5</TD><TD>4G5</TD><TD>3G6</TD><TD>4G6</TD><TD>3G7</TD><TD>4G7</TD><TD>3G8</TD><TD>4G8</TD><TD>3G9</TD><TD>4G9</TD><TD>3G10</TD><TD>4G10</TD><TD>3G11</TD><TD>4G11</TD><TD>3G12</TD><TD>4G12</TD></TR><TR><TD>1H1</TD><TD>2H1</TD><TD>1H2</TD><TD>2H2</TD><TD>1H3</TD><TD>2H3</TD><TD>1H4</TD><TD>2H4</TD><TD>1H5</TD><TD>2H5</TD><TD>1H6</TD><TD>2H6</TD><TD>1H7</TD><TD>2H7</TD><TD>1H8</TD><TD>2H8</TD><TD>1H9</TD><TD>2H9</TD><TD>1H10</TD><TD>2H10</TD><TD>1H11</TD><TD>2H11</TD><TD>1H12</TD><TD>2H12</TD></TR><TR><TD>3H1</TD><TD>4H1</TD><TD>3H2</TD><TD>4H2</TD><TD>3H3</TD><TD>4H3</TD><TD>3H4</TD><TD>4H4</TD><TD>3H5</TD><TD>4H5</TD><TD>3H6</TD><TD>4H6</TD><TD>3H7</TD><TD>4H7</TD><TD>3H8</TD><TD>4H8</TD><TD>3H9</TD><TD>4H9</TD><TD>3H10</TD><TD>4H10</TD><TD>3H11</TD><TD>4H11</TD><TD>3H12</TD><TD>4H12</TD></TR></TBODY></TABLE> Thu, 06 Sep 2018 13:18:00 GMT https://communities.sas.com/t5/SAS-Programming/Merging-2-datasets/m-p/493005#M129672 vseshad 2018-09-06T13:18:00Z Re: Merging 2 datasets https://communities.sas.com/t5/SAS-Programming/Merging-2-datasets/m-p/493006#M129673 <P>I haven't had a chance to test this code yet, I will try it and let you know if it works. But thank you so much for your reply.&nbsp;</P> Thu, 06 Sep 2018 13:20:41 GMT https://communities.sas.com/t5/SAS-Programming/Merging-2-datasets/m-p/493006#M129673 vseshad 2018-09-06T13:20:41Z