Hi ,
I have a dataset X which has these columns Apple1, Apple2, Orange1, Orange2. Another Dataset Y which looks like below in Dataset Y I have taken list of items I need to check in Data X whether they exist or not . Also, while checking when Apple1 is existing Orange1 should also exist in X dataset . Same thing when Apple2 is existing then Orange2 should also exist then only create output . I am using below code by using prxmatch but it doesnot work
test1 | test2 |
Apple1 | Orange1 |
Apple2 | Orange2 |
proc contents data= X noprint out= GTAB (keep=name varnum);
run;
Then created Y table which looks like below
test1 | test2 |
Apple1 | Orange1 |
Apple2 | Orange2 |
data _null_;
set Y;
call symput('wrd1'||left(_n_), strip(test1));
call symput('wrd2'||left(_n_), strip(test2));
CALL SYMPUT( 'Total' , _N_ );
run;
%MACRO LOOP;
data temp2;
set X ;
%do i=1 %to &Total;
if (prxmatch("/&&wrd1&i./",NAME) gt 0 and prxmatch("/&&wrd2&i./",NAME) gt 0) then output ;
%end;
run;
%mend;
%loop;
But this condition is not working and dataset temp2 is empty
Can anyone please help
Thanks
No need for macro logic to make the test.
data metadata ;
input (test1 test2) (:$32.);
cards;
Apple1 Orange1
Apple2 Orange2
;
data x;
length apple1 apple2 orange1 8;
run;
proc contents data= X noprint out= GTAB (keep=name varnum);
run;
proc sql ;
create table check1 as
select test1,test2
,(upcase(test1) in (select upcase(name) from gtab)) as check1
,(upcase(test2) in (select upcase(name) from gtab)) as check2
from metadata
;
select min(check1 and check2) into :all_pass trimmed from check1 ;
quit;
proc print data=check1;
run;
%put &=all_pass ;
You have 2 columns, they are called test1 and test2 .
Are you trying to check if the datasets are identical?
No need for macro logic to make the test.
data metadata ;
input (test1 test2) (:$32.);
cards;
Apple1 Orange1
Apple2 Orange2
;
data x;
length apple1 apple2 orange1 8;
run;
proc contents data= X noprint out= GTAB (keep=name varnum);
run;
proc sql ;
create table check1 as
select test1,test2
,(upcase(test1) in (select upcase(name) from gtab)) as check1
,(upcase(test2) in (select upcase(name) from gtab)) as check2
from metadata
;
select min(check1 and check2) into :all_pass trimmed from check1 ;
quit;
proc print data=check1;
run;
%put &=all_pass ;
Thank you so Much this one worked
Thank you
My GTAB table looks like below
Items |
Grape1 |
Apple1 |
Orange1 |
Grape2 |
Melon1 |
Apple2 |
Now what I am trying to do is to pull all items from GTAB based on the mapping from Y table I created . So, we have Apple1, Orange1, Apple2 exists in GTAB which is also in mapping table Y . So my end dataset what I am looking for is like below
test1 | test2 |
Apple1 | Orange1 |
I cant bring 2nd row from Y table because though we have Apple2 Orange 2 doesnot exist
Thanks
You seem very confused indeed.
Pay attention to what is called what.
Like this?
data X Y;
TEST1='Apple1'; TEST2='Orange1'; output;
TEST1='Apple2'; TEST2='Orange2'; output;
run;
proc contents data= X noprint out= GTAB (keep=name varnum);run;
data _null_;
set GTAB;
call symputx(catt('wrd1',_N_), TEST1);
call symputx(cats('wrd2',_N_), TEST2);
call symputx('total' , _N_ );
run;
data TEMP2;
set Y ;
do I=1 to &total.;
if prxmatch('/'||symget(cats('wrd1',I))||'/',TEST1)
and prxmatch('/'||symget(cats('wrd2',I))||'/',TEST2) then output;
end;
run;
NOTE: The data set WORK.TEMP2 has 4 observations and 3 variables.
Hi Chris ,
Thank you so much for taking time for helping me . I have tried this but here when you create Y table which doesn't have Orange1 in it . Then the output dataset should look like below but temp2 is empty this is what happening with my code too
TEST1 | TEST2 |
Apple2 | Orange2 |
Thank you so much for all the help
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.