BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hhchenfx
Barite | Level 11

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;

1 ACCEPTED SOLUTION

Accepted Solutions
s_lassen
Meteorite | Level 14

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.

View solution in original post

5 REPLIES 5
error_prone
Barite | Level 11
I can't see a beautiful way to solve this - haven't had a coffee, so that could be the cause of lacking good ideas. What have you tried so far? Are there always just two key-value-pairs in "name_value"? Is seems that transposing that dataset to have exactly one key-value-pair in each observation a good start.
s_lassen
Meteorite | Level 14

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.

gamotte
Rhodochrosite | Level 12

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;
Ksharp
Super User
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;

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 716 views
  • 2 likes
  • 6 in conversation