topic how to apply variable values in other table. in SAS Programming

how to apply variable values in other table.

sathya66 — Wed, 28 Mar 2018 09:21:23 GMT

Hi All,

I have a table called erased. It has only one column(100000 obs), based on those values ,I need to change the values in other table.(I have a macro to change the values) but need to apply the logic.

data Erased;
set lib.Erased(keep=id);
run;

proc sql noprint;
	select count(*) into :numb_ids
	from Erased;
	;
quit;

data names;
	set names;
	%all(name);/*this macro is changing the varibale vales in names table*/ /*but my condition should be if the values meets from the erased table then change the values in name table elase don't change*/
run;

/*this logic need to apply for the all the tables in all libraries*/

Thanks,

Re: how to apply variable values in other table.

Satish_Parida — Wed, 28 Mar 2018 10:02:55 GMT

Could you post the macro %all logic. It would be easy for us that way.

Re: how to apply variable values in other table.

sathya66 — Wed, 28 Mar 2018 10:12:24 GMT

Thanks Satish,

%macro var(ds,var);
		if &var ne ' ' then
			&var = "xxxxx";	
%mend var;


%macro all(ds);
%var(&ds,var);
%mend all;

Re: how to apply variable values in other table.

sathya66 — Wed, 28 Mar 2018 10:30:49 GMT

data names;
	set names;
	%all(name);
	where id in (Erased);/*((this kind of approach i am looking for)*/
run;

Re: how to apply variable values in other table.

Satish_Parida — Wed, 28 Mar 2018 10:31:46 GMT

Sorry, still not able to co-relate use of Erased(id) in names dataset.

The problem(macro var) you stated can be solved using a simple match merge or sql join.

Please elaborate your problem.

Thank you.

Re: how to apply variable values in other table.

sathya66 — Wed, 28 Mar 2018 10:44:49 GMT

a dataseet

Name Sex Age Height Weight id
Alfred M 14 69 112.5 1
Alice F 13 56.5 84 2
Barbara F 13 65.3 98 3
Carol F 14 62.8 102.5 4
Henry M 14 63.5 102.5 5
James M 12 57.3 83 6
Jane F 12 59.8 84.5 7
Janet F 15 62.5 112.5 8
Jeffrey M 13 62.5 84 9
John M 12 59 99.5 10
Joyce F 11 51.3 50.5 11
Judy F 14 64.3 90 12
Louise F 12 56.3 77 13
Mary F 15 66.5 112 14
Philip M 16 72 150 15
Robert M 12 64.8 128 16
Ronald M 15 67 133 17
Thomas M 11 57.5 85 18
William M 15 66.5 112 19

b dataset;

id
10
11
12
13
14
15
16
17
18
19

now I want repalce the values of name varable with XXXX. if the condition meets in b datasets(based on ID) elase don't change.

Re: how to apply variable values in other table.

sathya66 — Wed, 28 Mar 2018 10:54:11 GMT

below logic is working but I want to make that dynamic in macro

data d;
	set a;
	
	if id in(10,11,12,13,14,15,16,17,18,19) then %all(a) ;

run;

Re: how to apply variable values in other table.

Satish_Parida — Wed, 28 Mar 2018 10:54:25 GMT

%macro var(ds,chnge_var,chk_var);
	case when a.&chk_var. in (select distinct &chk_var. from &ds.) then "xxxxx" else &chnge_var. as &chnge_var.
%mend var;

proc sql;
create table names as
select %var(ds=lib.Erased,chnge_var=Name,chk_var=id), Sex, Age, Height, id
from names a;
quit;

Easy way:

data names;
merge names(in=a) lib.Erased(in=b);
by id;
if a=b then do;
	Name='xxxxxx';
end;
if a then output;
run;
/*Both dataset shorted by id*/

Re: how to apply variable values in other table.

Kurt_Bremser — Wed, 28 Mar 2018 10:55:53 GMT

proc sort data=a;
by id;
run;

proc sort data=b;
by id;
run;

data want;
merge
  a (in=a)
  b (in=b)
;
by id;
if a;
if b then name = 'XXXX';
run;