topic Re: To compare two input files in Data Management Studio 2.4 in SAS Data Management

To compare two input files in Data Management Studio 2.4

Shah — Wed, 27 May 2015 10:34:49 GMT

I have two input files in data job to compare the data. The requirement is, if the JC (field) is changed from previous file to current file (if the JC changes from alphanumeric character to numeric character) then it should display the output.

For e.g.

File 1 File 2

Previous Table			Current Table
PNO	JC	Name	PNO	JC	Name
111	CMR1	xxxx	111	20	xxxx
112	SAL2	yyyy	112	40	yyyy
113	20	zzzz	113	20	zzzz
114	40	aaaa	114	40	aaaa
115	CMR1	bbbb	115	CMR2	bbbb
116	CMR1	cccc	116	CMR3	cccc
117	20	dddd	117	20	dddd
118	20	eeee	120	20	llll
119	20	ffff	121	20	kkkk

For the above table the output should be as below becoz the data is changing from CMR1 to 20, i.e. alphanumeric to numeric.

PNO	JC	Name
111	20	xxxx
112	40	yyyy

Regards,

Shaheen

Re: To compare two input files in Data Management Studio 2.4

Shah — Mon, 08 Jun 2015 06:14:41 GMT

Hello All,

Any input or view point on the above question?

Regards,

Shaheen

Re: To compare two input files in Data Management Studio 2.4

Kurt_Bremser — Mon, 08 Jun 2015 07:17:51 GMT

Should any change be caught (ie 20 to 40), or just a change in type?

Re: To compare two input files in Data Management Studio 2.4

Shah — Mon, 08 Jun 2015 09:04:15 GMT

Its mainly change in type. No other changes.

Re: To compare two input files in Data Management Studio 2.4

Kurt_Bremser — Mon, 08 Jun 2015 10:45:39 GMT

This will detect changes from alpha to numeric:

data want (keep=PNO JC Name);

merge

file1 (in=in1 rename=(JC=JC_old))

file2 (in=in2 drop=Name)

;

by PNO;

if in1 and in2;

int_string = trim(JC_old); * get rid of leading blanks;

num = notdigit(int_string);

if num ne 0 and num ne length(int_string) + 1; * JC_old is alpha;

int_string = trim(JC); * get rid of leading blanks;

num = notdigit(int_string);

if num = 0 or num = length(int_string) + 1; * JC is numeric;

run;

Re: To compare two input files in Data Management Studio 2.4

data_null__ — Mon, 08 Jun 2015 11:31:20 GMT

You might want to check the documentation regarding TRIM function.

Re: To compare two input files in Data Management Studio 2.4

data_null__ — Mon, 08 Jun 2015 11:33:52 GMT

This might adequate and is pretty simple.

data Previous;
   input (PNO  JC Name)($);
   cards; 
111   CMR1  xxxx
112   SAL2  yyyy
113   20 zzzz
114   40 aaaa
115   CMR1  bbbb
116   CMR1  cccc
117   20 dddd
118   20 eeee
119   20 ffff
;;;;
   run; 
proc print; 
   run; 
data Current;
   input (PNO  JC Name)($);
   cards; 
111   20 xxxx
112   40 yyyy
113   20 zzzz
114   40 aaaa
115   CMR2  bbbb
116   CMR3  cccc
117   20 dddd
120   20 llll
121   20 kkkk
;;;;
   run; 
proc print; 
   run; 

data want;
   merge
      Previous(in=in1 rename=(JC=PrevJC))
      Current(in=in2  rename=(name=CurName))
      ;
   by PNO;
   if in1 and in2;
   n1 = input(prevjc,?? f8.);
   n2 = input(jc    ,?? f8.);
   if missing(n1) and not missing(n2);

    drop n1 n2;
   run; 
proc print; 
   run;

Re: To compare two input files in Data Management Studio 2.4

Kurt_Bremser — Mon, 08 Jun 2015 11:55:48 GMT

Yeah, right.

s/trim/strip/g

Re: To compare two input files in Data Management Studio 2.4

Ksharp — Mon, 08 Jun 2015 12:07:59 GMT

Code: Program

data Previous;
   input (PNO  JC Name)($);
   cards; 
111 CMR1 xxxx
112 SAL2 yyyy
113 20 zzzz
114 40 aaaa
115 CMR1 bbbb
116 CMR1 cccc
117 20 dddd
118 20 eeee
119 20 ffff
;;;;
   run; 

data Current;
   input (PNO  JC Name)($);
   cards; 
111 20 xxxx
112 40 yyyy
113 20 zzzz
114 40 aaaa
115 CMR2 bbbb
116 CMR3 cccc
117 20 dddd
120 20 llll
121 20 kkkk
;;;;
   run; 


data want;
   merge
   Previous( rename=(JC=PrevJC))
   Current(rename=(name=CurName))
   ;
   by PNO; 
if prxmatch('/^[a-z]+\d+$/i',strip(PrevJC)) and prxmatch('/^\d+$/i',strip(JC))  ;
   run;

Xia Keshan

Message was edited by: xia keshan

Message was edited by: xia keshan fix a mistake.

Re: To compare two input files in Data Management Studio 2.4

skillman — Tue, 09 Jun 2015 13:22:18 GMT

Shah,

Here is a solution in DM Studio. This solution works regardless of the field type on the input table. It scans each character and creates a profile of the field (JC). The data is joined and then an expression identifies where the field profile is different and outputs that data. The expression is quite simple:

`char_type_prev` != `char_type_curr`

The data job is also attached.

-shawn

Re: To compare two input files in Data Management Studio 2.4

Shah — Wed, 10 Jun 2015 09:32:59 GMT

Hi Shawn,

Please attach the ddf file once again, I am not able to open it. You have used job specific data node as input file and i am using data source node as input file as there are other fields which has to be populated. I didnt get the usage of expression. Can you please eloborate it?

Hi Xia,

The code you have written, ist specially for the input i have specified. My input will keep changing. There is no fixed input to use it in coding and also are you using the expression here, it seems to me as complete coding and in data flux we have nodes and expressions. I am not good at technical. Please help me in understanding.

And also TRIM function is not very usefull.

Regards,

Shaheen

Re: To compare two input files in Data Management Studio 2.4

skillman — Wed, 10 Jun 2015 13:12:23 GMT

Shah,

I just used the job specific data node for demonstration purposes. If you replace this with your data inputs, assuming the column names are the same, the logic will work as expected.

This is the expression logic used in the 'Field Profile' node:

/* Pre-Expression */

hidden real field_length

hidden integer cnt

string char_type

cnt = 1

field_length = len(`JC`)

char_type = ''

while cnt <= field_length

begin

if isnumber(mid(`JC`, cnt, 1))

char_type = char_type & ' ' & 'numeric'

else

char_type = char_type & ' ' & 'alpha'

cnt = cnt + 1

end

cnt = 1

What this expression does is loop through the entire value to determine if you have all numeric, all alpha or a mix (alphanumeric). Because your input (the JC Column) can be characters or numeric the column type is set to a character (string) field. You must look at a profile of the field's value instead of the field type based on your requirement.

-shawn

Message was edited by: shawn skillman Unable to reattach the DDF.

Re: To compare two input files in Data Management Studio 2.4

Shah — Wed, 17 Jun 2015 09:32:52 GMT

Hi Shawn,

The code is working perfectly fine. Thank you so much for the solution.

Regards,

Shaheen

Re: To compare two input files in Data Management Studio 2.4

ricardo_benatti — Fri, 26 Jun 2015 18:08:14 GMT

Hello,

I really liked your solution, very creative.

But it would be possible to use the function pattern(* string *), which returns something similar to the created by their EEL ( char_type_curr == W_PATTERN_curr 😞

PNO_curr	JC_curr	Name_curr	W_PATTERN_curr	char_type_curr	PNO_prev	JC_prev	Name_prev	W_TIPO_PREV	char_type_prev	W_PATTERN_prev
117	20	dddd	99	numeric numeric	117	20	dddd	integer	numeric numeric	99
116	CMR1	cccc	AAA9	alpha alpha alpha numeric	116	CMR3	cccc	string	alpha alpha alpha numeric	AAA9
115	CMR1	bbbb	AAA9	alpha alpha alpha numeric	115	CMR2	bbbb	string	alpha alpha alpha numeric	AAA9
114	40	aaaa	99	numeric numeric	114	40	aaaa	integer	numeric numeric	99
113	20	zzzz	99	numeric numeric	113	20	zzzz	integer	numeric numeric	99
112	SAL2	yyyy	AAA9	alpha alpha alpha numeric	112	40	yyyy	integer	numeric numeric	99
111	CMR1	xxxx	AAA9	alpha alpha alpha numeric	111	20	xxxx	integer	numeric numeric	99

This solution could also be a ??

Thanks,

Ricardo Benatti.

Re: To compare two input files in Data Management Studio 2.4

skillman — Fri, 26 Jun 2015 20:54:40 GMT

The code I supplied essentially created a user-defined function vs. using an existing function. Creating a user-defined function can enable the users to see what is happening behind the scenes with the pattern() function.

Either way will work.

Re: To compare two input files in Data Management Studio 2.4

Shah — Mon, 29 Jun 2015 02:58:51 GMT

Hi Ricardo,

The pattern() function is working in the same way. Thank you for giving alternate way to execute it.

Regards,

Shaheen

Re: To compare two input files in Data Management Studio 2.4

ricardo_benatti — Tue, 30 Jun 2015 11:41:30 GMT

so ...

also understand how you ...

EEL exemplifies the treatment attribute ...

the functions implemented by the solution should have a better performance ...

the intention was to contribute only ...

Regards,