topic Re: Matching columns from macro variable to dataset in SAS Programming

Matching columns from macro variable to dataset

jhh197 — Tue, 20 Nov 2018 21:07:32 GMT

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

Re: Matching columns from macro variable to dataset

34reqrwe — Wed, 21 Nov 2018 00:05:41 GMT

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

Are you trying to check if the datasets are identical?

Re: Matching columns from macro variable to dataset

Tom — Wed, 21 Nov 2018 01:06:51 GMT

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 ;

Re: Matching columns from macro variable to dataset

ChrisNZ — Wed, 21 Nov 2018 02:48:34 GMT

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.

Re: Matching columns from macro variable to dataset

jhh197 — Wed, 21 Nov 2018 03:17:29 GMT

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

Re: Matching columns from macro variable to dataset

jhh197 — Wed, 21 Nov 2018 15:40:25 GMT

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

Re: Matching columns from macro variable to dataset

jhh197 — Wed, 28 Nov 2018 19:02:02 GMT

Thank you so Much this one worked

Thank you

Re: Matching columns from macro variable to dataset

jhh197 — Wed, 28 Nov 2018 19:03:04 GMT

Thank you so much for all the help