Hi Everyone,
I have 2 files:
1 file contains variable name and variable value
1 file contain value for each variable and and Result variable.
I want to merge the 2 file so that for each row in file name_value, I can get all record that meet condition in file 1.
For example, for the first row '
1 a 1 b 2
the result should be
1 a 1 b 1 1 2 3 1000
1 a 1 b 1 1 2 1 999
2nd row has no record met condition
3rd row has 1 record
3 b 3 c 1 1 3 1 888
Any help is very much appreciated.
HHCFX
/*This code below will get data for each condition*******************/
data condition;
input condition_ID cond1 :$1. value1 cond2 :$1. value2;
datalines;
1 a 4 b 5
2 c 1 d 2
3 c 9 d 9
;run;
data FULLDATA;
input date a b c d ;
datalines;
11 4 5 1 1
12 4 5 2 5
13 4 1 2 6
14 8 3 1 2
15 4 5 1 2
;run;
filename tmp temp;
data _null_;
set condition end=last_cond;
file tmp;
put 'if ' cond1 '=' value1 'and ' cond2 '=' value2
' then do; condition_id=' condition_id '; output;end;' ;
run;
options source2;
data want;
set fulldata;
%include tmp;
if condition_id >=1;
run;
Here is a possible solution. I am not sure how fast it runs with real-life data, but it seems to work:
data want;
set name_value;
if 0 then set original; /* "declare" variables, for use with CALL SET later */
array names(*) var_name1-var_name2;
array values(*) var_value1-var_value2;
length cond $100;
do _N_=1 to dim(names);
call catx(' and ',cond,cats(names(_N_),'=',values(_N_)));
end;
dsid=open(cats('original(where=(',cond,'))'));
call set(dsid);
do while(fetch(dsid)=0);
output;
end;
dsid=close(dsid);
drop dsid;
run;
I did not drop the condition (COND variable), as it is nice to have for checking, at least initially.
Here is a possible solution. I am not sure how fast it runs with real-life data, but it seems to work:
data want;
set name_value;
if 0 then set original; /* "declare" variables, for use with CALL SET later */
array names(*) var_name1-var_name2;
array values(*) var_value1-var_value2;
length cond $100;
do _N_=1 to dim(names);
call catx(' and ',cond,cats(names(_N_),'=',values(_N_)));
end;
dsid=open(cats('original(where=(',cond,'))'));
call set(dsid);
do while(fetch(dsid)=0);
output;
end;
dsid=close(dsid);
drop dsid;
run;
I did not drop the condition (COND variable), as it is nice to have for checking, at least initially.
Hello,
I think this should work :
data name_value;
set name_value;
array abc (3) 4. a b c;
do i=1 to dim(abc);
if var_name1=vname(abc(i)) then abc(i)=var_value1;
if var_name2=vname(abc(i)) then abc(i)=var_value2;
end;
run;
proc sql noprint;
CREATE TABLE want AS
SELECT a.name_ID, var_name1, var_value1, var_name2, var_value2, b.*
FROM name_value a
LEFT JOIN original b
ON (a.a=b.a OR a.a=.) AND (a.b=b.b OR a.b=.) AND (a.c=b.c OR a.c=.);
quit;
data name_value;
input name_ID var_name1 $ var_value1 var_name2 $ var_value2;
datalines;
1 a 1 b 2
2 b 1 c 0
3 b 3 c 1
;run;
data original;
input a b c result;
datalines;
1 2 3 1000
1 2 1 9999
1 3 1 8888
0 0 9 1000
;run;
data k;
set original;
length var_name1 var_name2 $ 32;
array x{*} a--c;
do i=1 to dim(x);
do j=i+1 to dim(x);
var_name1=vname(x{i});var_value1=x{i};
var_name2=vname(x{j});var_value2=x{j};
output;
var_name1=vname(x{j});var_value1=x{j};
var_name2=vname(x{i});var_value2=x{i};
output;
end;
end;
drop i j;
run;
data want;
if _n_=1 then do;
if 0 then set k;
declare hash k(dataset:'k',multidata:'y');
k.definekey('var_name1','var_value1','var_name2','var_value2');
k.definedata(all:'y');
k.definedone();
end;
set name_value;
rc=k.find();
if rc ne 0 then output;
do while (rc=0);
output;
rc=k.find_next();
end;
call missing(of _all_);
drop rc;
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.