DATA Step, Macro, Functions and more

SQL on name of variable and value of variable

Accepted Solution Solved
Reply
Super Contributor
Posts: 503
Accepted Solution

SQL on name of variable and value of variable

[ Edited ]

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;


Accepted Solutions
Solution
‎02-06-2018 11:56 AM
PROC Star
Posts: 227

Re: SQL on name of variable and value of variable

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


All Replies
Super User
Posts: 9,556

Re: SQL on name of variable and value of variable

And what is the condition?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Regular Contributor
Posts: 195

Re: SQL on name of variable and value of variable

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.
Solution
‎02-06-2018 11:56 AM
PROC Star
Posts: 227

Re: SQL on name of variable and value of variable

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.

Super Contributor
Posts: 320

Re: SQL on name of variable and value of variable

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;
Super User
Posts: 10,610

Re: SQL on name of variable and value of variable

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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