cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BookmarkSubscribeRSS Feed
vseshad
Calcite | Level 5 vseshad
Calcite | Level 5

Merging 2 datasets

Posted 09-05-2018 09:30 PM (1189 views)

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 :

PlateWell
1A02
2A02
3A02
4A02
1A03
2A03
3A03
4A03


portion of Dataset i need:

PlateWellRowColumnnew plate no
1A02A31
2A02A41
3A02B31
4A02B41
1A03A51
2A03A61
3A03B51
4A03B61

 

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.

0 Likes
Reply
5 REPLIES 5
Reeza
Super User Reeza
Super User

Re: Merging 2 datasets

Posted 09-05-2018 10:26 PM (1179 views)  |   In reply to vseshad

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;

 

 

0 Likes
Reply
vseshad
Calcite | Level 5 vseshad
Calcite | Level 5

Re: Merging 2 datasets

Posted 09-06-2018 09:18 AM (1142 views)  |   In reply to Reeza

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!

 

1A12A11A22A21A32A31A42A41A52A51A62A61A72A71A82A81A92A91A102A101A112A111A122A12
3A14A13A24A23A34A33A44A43A54A53A64A63A74A73A84A83A94A93A104A103A114A113A124A12
1B12B11B22B21B32B31B42B41B52B51B62B61B72B71B82B81B92B91B102B101B112B111B122B12
3B14B13B24B23B34B33B44B43B54B53B64B63B74B73B84B83B94B93B104B103B114B113B124B12
1C12C11C22C21C32C31C42C41C52C51C62C61C72C71C82C81C92C91C102C101C112C111C122C12
3C14C13C24C23C34C33C44C43C54C53C64C63C74C73C84C83C94C93C104C103C114C113C124C12
1D12D11D22D21D32D31D42D41D52D51D62D61D72D71D82D81D92D91D102D101D112D111D122D12
3D14D13D24D23D34D33D44D43D54D53D64D63D74D73D84D83D94D93D104D103D114D113D124D12
1E12E11E22E21E32E31E42E41E52E51E62E61E72E71E82E81E92E91E102E101E112E111E122E12
3E14E13E24E23E34E33E44E43E54E53E64E63E74E73E84E83E94E93E104E103E114E113E124E12
1F12F11F22F21F32F31F42F41F52F51F62F61F72F71F82F81F92F91F102F101F112F111F122F12
3F14F13F24F23F34F33F44F43F54F53F64F63F74F73F84F83F94F93F104F103F114F113F124F12
1G12G11G22G21G32G31G42G41G52G51G62G61G72G71G82G81G92G91G102G101G112G111G122G12
3G14G13G24G23G34G33G44G43G54G53G64G63G74G73G84G83G94G93G104G103G114G113G124G12
1H12H11H22H21H32H31H42H41H52H51H62H61H72H71H82H81H92H91H102H101H112H111H122H12
3H14H13H24H23H34H33H44H43H54H53H64H63H74H73H84H83H94H93H104H103H114H113H124H12
0 Likes
Reply
Astounding
PROC Star Astounding
PROC Star

Re: Merging 2 datasets

Posted 09-05-2018 10:29 PM (1178 views)  |   In reply to vseshad

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.  

0 Likes
Reply
vseshad
Calcite | Level 5 vseshad
Calcite | Level 5

Re: Merging 2 datasets

Posted 09-06-2018 09:20 AM (1140 views)  |   In reply to Astounding

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. 

0 Likes
Reply
Reeza
Super User Reeza
Super User

Re: Merging 2 datasets

Posted 09-05-2018 10:37 PM (1171 views)  |   In reply to vseshad

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;
0 Likes
Reply

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Watch How to Concatenate Values on YouTube

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • ‎09-05-2018 09:30 PM
  • 1190 views
  • 0 likes
  • 3 in conversation