topic Data Manipulation and clean up in SAS Programming

Data Manipulation and clean up

NikosStratis — Tue, 11 Feb 2020 07:19:08 GMT

Hello team!

I have brainteaser… I need to clean up a file with extra entries. I have the following data:

data work.have;

infile datalines delimiter=',';

length Field_A $8 Field_B $8;

input Field_A $ Field_B $;

datalines;

13976907,12555848

12555848,13976907

13589994,11280580

11280580,13589994

10947277,10808526

10808526,10947277

12334829,92335000

92335000,12334829

;

run;

What I need to do is the following:

When Field_A from row 1 is equal to Field_B from row 2

Then I need to keep only row 1.

The data comes in pairs of two rows and it is like this,

row1.Field_a = row2.Filed_B AND row1.Field_B = Row2.Field_A

my output shall be:

13976907,12555848

13589994,11280580

10947277,10808526

12334829,92335000

I haven’t done anything like that in the past… can this bee accomplished?

Any help is welcomed!

Thank you in advance!

Re: Data Manipulation and clean up

ed_sas_member — Tue, 11 Feb 2020 08:54:08 GMT

Hi @NikosStratis

Does this code meet your expectations:

data want (drop=Field_B_2);
	merge have have (keep=Field_B rename=(Field_B = Field_B_2) firstobs=2);
	if Field_A = Field_B_2 then output;
run;

Re: Data Manipulation and clean up

Kurt_Bremser — Tue, 11 Feb 2020 08:58:27 GMT

By using the lag() function, and modulo of _n_:

data work.have;
infile datalines delimiter=','; 
length Field_A $8 Field_B $8;
input Field_A $ Field_B $;
datalines;
13976907,12555848
12555848,13976907
13589994,11280580
11280580,13589994
10947277,10808526
10808526,10947277
12334829,92335000
92335000,12334829
;

data want;
set have;
if mod(_n_,2) = 0 and lag(field_a) = field_b then delete;
run;

proc print data=want noobs;
run;

Result:

Field_A     Field_B

13976907    12555848
13589994    11280580
10947277    10808526
12334829    92335000

Please use the "little running man" for posting code (as I did), so that the code is kept as-is. Posting code into the main posting window will cause unwanted changes (extra lines, smileys, ...)

Re: Data Manipulation and clean up

NikosStratis — Tue, 11 Feb 2020 09:07:05 GMT

Thank you so much!

Iτ ςορκσ ξθστ φινε!

Re: Data Manipulation and clean up

NikosStratis — Tue, 11 Feb 2020 09:07:30 GMT

It works just fine!

Re: Data Manipulation and clean up

gamotte — Tue, 11 Feb 2020 09:12:16 GMT

The data comes in pairs of two rows and it is like this,

row1.Field_a = row2.Filed_B AND row1.Field_B = Row2.Field_A

So you just need to drop even rows :

data work.have;
infile datalines delimiter=','; 
length Field_A $8 Field_B $8;
input Field_A $ Field_B $;

if mod(_N_,2);

datalines;
13976907,12555848
12555848,13976907
13589994,11280580
11280580,13589994
10947277,10808526
10808526,10947277
12334829,92335000
92335000,12334829
;
run;

Re: Data Manipulation and clean up

Ksharp — Tue, 11 Feb 2020 10:58:05 GMT

data work.have;
infile datalines delimiter=','; 
length Field_A $8 Field_B $8;
input Field_A $ Field_B $;
datalines;
13976907,12555848
12555848,13976907
13589994,11280580
11280580,13589994
10947277,10808526
10808526,10947277
12334829,92335000
92335000,12334829
;

data temp;
 set have;
 call sortc(field_a,field_b);
run;
proc sort data=temp out=want nodupkey;
by field_a field_b;
run;

Re: Data Manipulation and clean up

gamotte — Tue, 11 Feb 2020 11:28:07 GMT

The accepted answer is displayed just after the question in the thread so that interested readers can find it quickly.

You should thus select the answer that solved your problem, here , i think @ed_sas_member 's or @Kurt_Bremser 's.