BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jhh197
Pyrite | Level 9

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

 

test1test2
Apple1Orange1
Apple2Orange2

 

proc contents data= X noprint out= GTAB (keep=name varnum);

run;

 

Then created Y table which looks like below

test1test2
Apple1Orange1
Apple2Orange2

 

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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 ;

View solution in original post

7 REPLIES 7
34reqrwe
Quartz | Level 8

You have 2 columns, they are called test1 and test2 . 

 

Are you trying to check if the datasets are identical?

 

 

Tom
Super User Tom
Super User

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 ;
jhh197
Pyrite | Level 9

Thank you so Much this one worked

 

 

Thank you

jhh197
Pyrite | Level 9

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

 

test1test2
Apple1Orange1

 

I cant bring 2nd row from Y table because though we have Apple2 Orange 2 doesnot exist

 

Thanks

ChrisNZ
Tourmaline | Level 20

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.

 

jhh197
Pyrite | Level 9

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

 

TEST1TEST2
Apple2Orange2

 

 

jhh197
Pyrite | Level 9

Thank you so much for all the help

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 2244 views
  • 0 likes
  • 4 in conversation