Merging 2 datasets

[vseshad]

Hello all,

I am an occasional dabbler of SAS and I am currently stuck on trying to find a solution to my problem.

I have a rather large 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. 

Part of my Dataset :

Plate	Well
1	A02
2	A02
3	A02
4	A02
1	A03
2	A03
3	A03
4	A03

portion of Dataset i need:

Plate	Well	Row	Column	new plate no
1	A02	A	3	1
2	A02	A	4	1
3	A02	B	3	1
4	A02	B	4	1
1	A03	A	5	1
2	A03	A	6	1
3	A03	B	5	1
4	A03	B	6	1

Every plate in the original dataset has 80 observations and I want to combine 4x80 into 1 plate with 320 values, combine the next 320 values into a second plate and so on. 

My original plan was to create a second dataset with the additional 3 observations and do a one to one merge, save it, delete those observations from original dataset and repeat but I have too many observations to do it this way 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.

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.

[Reeza]

Can you explain the logic for the Column calculation? Not quite following that.

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;

[vseshad]

I'm sorry I wasn't very clear.

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, A01 of plate3 becomes B01 of newplate 1, and A01 of plate4  becomes B02 of newplate1. 

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!

1A1

2A1

1A2

2A2

1A3

2A3

1A4

2A4

1A5

2A5

1A6

2A6

1A7

2A7

1A8

2A8

1A9

2A9

1A10

2A10

1A11

2A11

1A12

2A12

3A1

4A1

3A2

4A2

3A3

4A3

3A4

4A4

3A5

4A5

3A6

4A6

3A7

4A7

3A8

4A8

3A9

4A9

3A10

4A10

3A11

4A11

3A12

4A12

1B1

2B1

1B2

2B2

1B3

2B3

1B4

2B4

1B5

2B5

1B6

2B6

1B7

2B7

1B8

2B8

1B9

2B9

1B10

2B10

1B11

2B11

1B12

2B12

3B1

4B1

3B2

4B2

3B3

4B3

3B4

4B4

3B5

4B5

3B6

4B6

3B7

4B7

3B8

4B8

3B9

4B9

3B10

4B10

3B11

4B11

3B12

4B12

1C1

2C1

1C2

2C2

1C3

2C3

1C4

2C4

1C5

2C5

1C6

2C6

1C7

2C7

1C8

2C8

1C9

2C9

1C10

2C10

1C11

2C11

1C12

2C12

3C1

4C1

3C2

4C2

3C3

4C3

3C4

4C4

3C5

4C5

3C6

4C6

3C7

4C7

3C8

4C8

3C9

4C9

3C10

4C10

3C11

4C11

3C12

4C12

1D1

2D1

1D2

2D2

1D3

2D3

1D4

2D4

1D5

2D5

1D6

2D6

1D7

2D7

1D8

2D8

1D9

2D9

1D10

2D10

1D11

2D11

1D12

2D12

3D1

4D1

3D2

4D2

3D3

4D3

3D4

4D4

3D5

4D5

3D6

4D6

3D7

4D7

3D8

4D8

3D9

4D9

3D10

4D10

3D11

4D11

3D12

4D12

1E1

2E1

1E2

2E2

1E3

2E3

1E4

2E4

1E5

2E5

1E6

2E6

1E7

2E7

1E8

2E8

1E9

2E9

1E10

2E10

1E11

2E11

1E12

2E12

3E1

4E1

3E2

4E2

3E3

4E3

3E4

4E4

3E5

4E5

3E6

4E6

3E7

4E7

3E8

4E8

3E9

4E9

3E10

4E10

3E11

4E11

3E12

4E12

1F1

2F1

1F2

2F2

1F3

2F3

1F4

2F4

1F5

2F5

1F6

2F6

1F7

2F7

1F8

2F8

1F9

2F9

1F10

2F10

1F11

2F11

1F12

2F12

3F1

4F1

3F2

4F2

3F3

4F3

3F4

4F4

3F5

4F5

3F6

4F6

3F7

4F7

3F8

4F8

3F9

4F9

3F10

4F10

3F11

4F11

3F12

4F12

1G1

2G1

1G2

2G2

1G3

2G3

1G4

2G4

1G5

2G5

1G6

2G6

1G7

2G7

1G8

2G8

1G9

2G9

1G10

2G10

1G11

2G11

1G12

2G12

3G1

4G1

3G2

4G2

3G3

4G3

3G4

4G4

3G5

4G5

3G6

4G6

3G7

4G7

3G8

4G8

3G9

4G9

3G10

4G10

3G11

4G11

3G12

4G12

1H1

2H1

1H2

2H2

1H3

2H3

1H4

2H4

1H5

2H5

1H6

2H6

1H7

2H7

1H8

2H8

1H9

2H9

1H10

2H10

1H11

2H11

1H12

2H12

3H1

4H1

3H2

4H2

3H3

4H3

3H4

4H4

3H5

4H5

3H6

4H6

3H7

4H7

3H8

4H8

3H9

4H9

3H10

4H10

3H11

4H11

3H12

4H12

[Astounding]

Just looking at what you posted (it's dangerous to open posted spreadsheets), here's an approach:

data want;

set have;

if mod(_n_, 80) = 1 then do;

   new_plate + 1;

   well_count = 2;

end;

else if mod(_n_, 4) = 1 then well_count + 2;

if mod(_n_, 2)  in (1, 3) then column = well_count + 1;

else column = well_count + 2;

retain row 'A';

back1 = lag(row);

back2 = lag2(row);

if back1 = back2 = 'A' then row='B';

else if back1=back2='B' then row='A';

drop well_count;

run;

It's untested code, and done without seeing the spreadsheet, so may need a little tweaking.  It assumes there are always four observations for a well.  

[vseshad]

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. 

[Reeza]

Assuming col is just alternating numbers offset by 2 for some reason, this works on your sample data at least:

*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;