Combining 2 data - different column is picked

[hhchenfx]

Hi Everyone,

I have 2 datasets:

- Fulldata 

- Criteria

For each row in Criteria, I want to pick all records in Fulldata meeting that condition.

In the sample data, there are 3 rows, so I create 3 data file (c1 c2 c3)

Then I change the name in these data and put them together.

In my real problem, there are 50+ criteria and I need your help to make a real code to deal with it.

Thank you,

HHC

data fulldata; input id var1 var2 var3;
datalines;
1 4 5 6
1 41 51 61
1 42 53 63
2 7 8 9
2 71 81 91
2 72 82 92
2 73 83 93
3 1 2 3
3 11 22 33
4 55 66 77
;run;

data criteria; input id var_to_keep $;
datalines;
1 var2
2 var3
3 var3
;run;

*use condition in the 1st row of criteria dataset;
data c1(rename=(var2=var)); set fulldata;
keep id var2;
if id=1;run;

*use condition in the 2nd row of criteria dataset;
data c2(rename=(var3=var)); set fulldata;
keep id var3;
if id=2;run;

*use condition in the 3rd row of criteria dataset;
data c3(rename=(var3=var)); set fulldata;
keep id var3;
if id=3;run;

*Put together;
data want; set c1 c2 c3;run;

[japelin]

You can use a macro to repeat the process.

%Macro Msetfulldata;
  data _null_;/* set criteria value to macro variables */
    set criteria end=eof;
    call symputx(cats('id',_n_),id);
    call symputx(cats('vtk',_n_),var_to_keep);
    if eof then call symputx('obs',_n_);
  run;
  data want;
    delete;
  run;
  %do i=1 %to &obs;
    data tmp; 
      set fulldata(keep=id &&vtk&i rename=(&&vtk&i=var));
      where id=&&id&i;/* if is also OK */
    run;
    data want;
      set want tmp;
    run;
  %end;
%Mend;
%Msetfulldata;

[Tom · Accepted solution]

So you just want to get the value of the variable named in VAR_TO_KEEP?

data want2 ;
  merge fulldata criteria(in=in2) ;
  by id;
  if in2;
  var = input(vvaluex(var_to_keep),32.);
run;

Let's compare the results:

proc compare data=want compare=want2;
run;

The COMPARE Procedure
Comparison of WORK.WANT with WORK.WANT2
(Method=EXACT)

Data Set Summary

Dataset              Created          Modified  NVar    NObs

WORK.WANT   30MAR21:23:51:58  30MAR21:23:51:58     2       9
WORK.WANT2  30MAR21:23:51:58  30MAR21:23:51:58     6       9


Variables Summary

Number of Variables in Common: 2.
Number of Variables in WORK.WANT2 but not in WORK.WANT: 4.


Observation Summary

Observation      Base  Compare

First Obs           1        1
Last  Obs           9        9

Number of Observations in Common: 9.
Total Number of Observations Read from WORK.WANT: 9.
Total Number of Observations Read from WORK.WANT2: 9.

Number of Observations with Some Compared Variables Unequal: 0.
Number of Observations with All Compared Variables Equal: 9.

NOTE: No unequal values were found. All values compared are exactly equal.

[Tom]

Note if you are worried about loss of precision caused by transforming VAR2, VAR3, etc into their formatted value and then back to numbers then attach the HEX16 format to them in that step and use the HEX16 informat in the INPUT() function call.

data want2 ;
  merge fulldata criteria(in=in2) ;
  by id;
  if in2;
  format var1-var3 hex16.;
  drop var1-var3;
  var = input(vvaluex(var_to_keep),hex16.);
run;

[hhchenfx]

Thank you, Tom for helping.

HHC