DATA Step, Macro, Functions and more

how to apply variable values in other table.

Accepted Solution Solved
Reply
Contributor
Posts: 64
Accepted Solution

how to apply variable values in other table.

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,

SS


Accepted Solutions
Solution
‎03-28-2018 07:30 AM
Frequent Contributor
Posts: 109

Re: how to apply variable values in other table.

%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*/

View solution in original post


All Replies
Frequent Contributor
Posts: 109

Re: how to apply variable values in other table.

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

Re: how to apply variable values in other table.

Posted in reply to Satish_Parida

Thanks Satish,

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


%macro all(ds);
%var(&ds,var);
%mend all;
Contributor
Posts: 64

Re: how to apply variable values in other table.

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

Re: how to apply variable values in other table.

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.

 

 

Contributor
Posts: 64

Re: how to apply variable values in other table.

Posted in reply to Satish_Parida

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.

Contributor
Posts: 64

Re: how to apply variable values in other table.

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;
Solution
‎03-28-2018 07:30 AM
Frequent Contributor
Posts: 109

Re: how to apply variable values in other table.

%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*/
Super User
Posts: 9,560

Re: how to apply variable values in other table.

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 104 views
  • 0 likes
  • 3 in conversation