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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.