topic Re: Macros warnings and errors in SAS Data Management

Macros warnings and errors

aaaaa34 — Sun, 17 Nov 2019 12:48:51 GMT

Hi,

I have warning related with my code.

Firstly, I create datasets. Secondly, I want to choose good values of "univer" variable (only these contain "place" variable). Finally, I want to join these good values with "rate" variable but I don't know how to do this without creating new set- SAS shows:

This CREATE TABLE statement recursively references the target table. A consequence of this is a possible data integrity

problem.

data aaa;
input  univer $50. ;
datalines;
wse and 
uww should be chosen 
we dont't have any values 
;
run;
data bbb;
input  place $ rate 1.;
datalines;
wse 8 
uww 8 
eel 9
;
run;
%macro first(data_out=);
%let list_place= wse!uww!ee1;
data xxx_place;
	set aaa;
	%let i_1=1;		
	%do %until (%qscan(&list_place,&i_1,'!') = %str());              
    	%let v1=%scan(&list_place,&i_1,'!');
    		find(univer,"&v1") >0 or
	    %let i_1=%eval(&i_1+1);
	%end;
	choice=1;
run;
proc sql;
create table xxx_place as
  select a.*,b.*
  from xxx_place a inner join bbb b
    on find(a.univer,b.place,'it')
  order by 1,2,3
;
quit;

data &data_out;
set xxx_place;
run;
%mend;

%first(data_out=mydata);

Re: Macros warnings and errors

Kurt_Bremser — Sun, 17 Nov 2019 13:25:33 GMT

I really don't see the necessity of a macro here, as the sole parameter is used exactly once in the code. A simple %let data_out= would do the same.

And your WARNING has nothing to do with the macro, it comes from potentially dangerous SQL code. You should not create a table in a select from that same table, create a new table instead:

proc sql;
create table &data_out. as
  select a.*,b.*
  from xxx_place a inner join bbb b
    on find(a.univer,b.place,'it')
  order by 1,2,3
;
quit;

That way you also don't need the last data step.

Re: Macros warnings and errors

aaaaa34 — Sun, 17 Nov 2019 13:41:51 GMT

Yes, I understand that macro is not necessery in this case. But I really want to have it there. Is it other option to add "rate" without using proc sql?

Re: Macros warnings and errors

Kurt_Bremser — Sun, 17 Nov 2019 16:37:45 GMT

What is the expected result out of your initial two datasets?

Re: Macros warnings and errors

aaaaa34 — Sun, 17 Nov 2019 17:08:38 GMT

Hi,

That's what I want to obtain:

Re: Macros warnings and errors

Kurt_Bremser — Mon, 18 Nov 2019 08:03:24 GMT

If you want to do a more complex lookup without using SQL, a hash object comes in handy:

data aaa;
input univer $50.;
datalines;
wse and 
uww should be chosen 
we dont't have any values 
;
run;

data bbb;
input place $ rate;
datalines;
wse 8 
uww 5 
eel 9
;
run;

data want;
length
  univer $50
  place $8
  rate 8
;
if _n_ = 1
then do;
  declare hash lookup(dataset:"bbb");
  lookup.definekey("place");
  lookup.definedata("rate");
  lookup.definedone();
end;
call missing(place,rate);
set aaa;
do i = 1 to countw(univer);
  place = scan(univer,i);
  rc = lookup.find();
  put rc= place=;
end;
if rate;
drop rc i place;
run;