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
%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*/
Thanks Satish,
%macro var(ds,var);
if &var ne ' ' then
&var = "xxxxx";
%mend var;
%macro all(ds);
%var(&ds,var);
%mend all;
data names;
set names;
%all(name);
where id in (Erased);/*((this kind of approach i am looking for)*/
run;
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.
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.
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;
%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*/
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.