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;
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
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
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.
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
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
;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.