BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sathya66
Barite | Level 11

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

1 ACCEPTED SOLUTION

Accepted Solutions
Satish_Parida
Lapis Lazuli | Level 10
%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

8 REPLIES 8
Satish_Parida
Lapis Lazuli | Level 10
Could you post the macro %all logic. It would be easy for us that way.
sathya66
Barite | Level 11

Thanks Satish,

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


%macro all(ds);
%var(&ds,var);
%mend all;
sathya66
Barite | Level 11
data names;
	set names;
	%all(name);
	where id in (Erased);/*((this kind of approach i am looking for)*/
run;
Satish_Parida
Lapis Lazuli | Level 10

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.

 

 

sathya66
Barite | Level 11

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.

sathya66
Barite | Level 11

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;
Satish_Parida
Lapis Lazuli | Level 10
%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*/

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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