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;
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.
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;
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.
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;
Thank you, Tom for helping.
HHC
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.