topic Re: Merge value of 1 variable value with another variable name in SAS Programming

Merge value of 1 variable value with another variable name

hhchenfx — Fri, 28 Jun 2013 02:40:28 GMT

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;

Re: Merge value of 1 variable value with another variable name

Haikuo — Fri, 28 Jun 2013 03:30:15 GMT

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;

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

Re: Merge value of 1 variable value with another variable name

hhchenfx — Fri, 28 Jun 2013 03:49:02 GMT

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

Re: Merge value of 1 variable value with another variable name

Reeza — Fri, 28 Jun 2013 04:23:45 GMT

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.

Re: Merge value of 1 variable value with another variable name

Ksharp — Fri, 28 Jun 2013 05:42:06 GMT

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

Re: Merge value of 1 variable value with another variable name

hhchenfx — Fri, 28 Jun 2013 19:44:12 GMT

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
;