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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.