topic Re: SQL on name of variable and value of variable in SAS Programming

SQL on name of variable and value of variable

hhchenfx — Tue, 06 Feb 2018 16:59:29 GMT

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;

Re: SQL on name of variable and value of variable

Kurt_Bremser — Tue, 06 Feb 2018 07:27:03 GMT

And what is the condition?

Re: SQL on name of variable and value of variable

error_prone — Tue, 06 Feb 2018 07:31:37 GMT

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.

Re: SQL on name of variable and value of variable

s_lassen — Tue, 06 Feb 2018 11:49:13 GMT

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.

Re: SQL on name of variable and value of variable

gamotte — Tue, 06 Feb 2018 12:56:16 GMT

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;

Re: SQL on name of variable and value of variable

Ksharp — Tue, 06 Feb 2018 13:07:20 GMT

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;