Merge value of 1 variable value with another variable name

[hhchenfx]

Hi Everyone,

I have 2 files:

File 1: Tracking value of variable (A B C D E…) overtime

Date A B
1 4 5
2 1 5
3 2 1
4 8 3 

File 2:  have the critical value for each variable
Name  Critical
A  3
A  2
B  3
B  7

I want to combine 2 files to get the date where a variable is above critical value

Name   Critical  Date
A  3  1
A  3  4
A  4  4
B  3  1
B  3  2

I don’t know how to do that kind of merge and very appreciate if you could help.

Thank you,

HHC

data time;
   input date a b;
   datalines;
1 4 5
2 1 5
3 2 1
4 8 3

   data critical;
   input name $ critical;
   datalines;
A  3
A  2
B  3
B  7

;;;;
   run;

[Haikuo]

I envision that you maybe able to do it by transposing one of your tables first, then Proc SQL.  But here is a Hash approach:

data time;

   input date a b;

   datalines;

1 4 5

2 1 5

3 2 1

4 8 3

;

   data critical;

   input name $ critical;

   datalines;

A  3

A  4

B  3

B  7

;

  data _null_;

      if _n_=1 then do;

    if 0 then set critical;

  declare hash h(dataset:'critical', multidata:'y');

  h.definekey('name');

  h.definedata(all:'y');

  h.definedone();

  declare hash want(multidata:'y',ordered:'a');

  want.definekey('name','critical');

  want.definedata('name','critical','date');

  want.definedone();

  end;

  set time end=last;

   array ab a b;

     do over ab;

    do rc=h.find(key:upcase(vname(ab))) by 0 while (rc=0);

       if critical < ab then want.replace();

   rc=h.find_next(key:upcase(vname(ab))) ;

  end;

  end;

  if last then want.output(dataset:'want');

  run;

BTW, I supposed that you have a typo in 'critical' table, the second row 'critical' value should be '4' instead of '2' per your output.

Haikuo

[hhchenfx]

Thank you, Haikuo.

You are right. The Second row critical value should be 4.

To tell you the truth, I am 100% blank reading your code 🙂

I will learn.

In the mean time, if other members have any other approach, please help.

Thank you,

HHC

[Reeza]

Does your output match your data?

You have two critical values for A, how does that work in terms of date?

I think a SQL merge would be simple enough, but having a hard time figuring out what your requirements are.

[Ksharp]

Here is SQL version:

data time;
   input date a b;
   datalines;
1 4 5
2 1 5
3 2 1
4 8 3
;

   data critical;
   input name $ critical;
   datalines;
A  3
A  4
B  3
B  7
;

proc transpose data=time out=temp ;
by date;
var a b;
run;
proc sql;
 create table want as
  select name,critical,date
   from critical,temp
    where name=upcase(_name_) and col1 gt critical
     order by Name,Critical  ;
quit;

Ksharp

[hhchenfx]

Hi Reeza  and Ksharp,

I face this problem after working on the one Ksharp helped me last time, where I create 2 name variables for the combination.

Actually, on critical file, there are 2 conditions for Variable A and Variable B as below. Therefore I don't think the SQL will help.

(So my effort to simplify this problem misleading this time)

So the output in this case should be

name1 name2 critical1 critical2 date

A B 3 2  1

A B 3 2  4

A C 3 12 4

Thank you for your help again.

HHC

data time;
   input date a b c;
   datalines;
1 4 5 2
2 1 5 8
3 2 1 9
4 8 3 50
;

   data critical;
   input name1 $ name2 $ critical1 critical2;
   datalines;
A B 3 2
A C 3 12
;