Replace if a macro value with a value

[gsk]

How do we use macro objects/variables when we have a list of values we want to replace with?

For example, %list contains 001 002 003 004. Let's say we have subject IDs like this:

SUBJ-001-M

SUBJ-002-J

SUBJ-005-K

SUBJ-007-K

SUBJ-004-N

I want to say: replace the number between - and - to single - if the number is one of the macro objects, and eventually end up with: 

SUBJ-M

SUBJ-J

SUBJ-005-K

SUBJ-007-K

SUBJ-N

[SASKiwi]

A good way is to construct your list of values in a SAS dataset then use PROC SQL to write this out to a macro variable:

data MySASDataset;
input @1 MyVar $10.;
cards;
SUBJ-M
SUBJ-J
SUBJ-005-K
SUBJ-007-K
SUBJ-N
;
run;

proc sql noprint;
  select MyVar
  into :MyMacroVar separated by " " 
  from MySASDataset
  ;
quit;

[Reeza]

Can you assume each line will have at most one number This is pretty well structured so it could be simplified quite a bit if needed.

[PaigeMiller]

Why a macro? Seems like you can do this easily in a data step. 

[gsk]

How? I don’t think tranwrd or replace would work because they only work with certain expression (not macro containing a list of values)

[gsk]

Thank you for the response! I meant how do we get to MySASDataset if we have a list of values stored in &list?

[gsk]

Yes, at most one number/match.

[unison · Accepted solution]

Hey gsk, is this what you mean? I agree that a data step is most suitable but I wrote a macro as well. It's all very much tailored this this situation, not at all general.

%let list = 001 002 003 004;

data have;
input @1 subjects $10.;
cards;
SUBJ-001-M
SUBJ-002-J
SUBJ-005-K
SUBJ-007-K
SUBJ-004-N
;
run;

data want(drop=s1 s2 s3);
	set have;
	s1 = scan(subjects,1,"-");
	s2 = scan(subjects,2,"-");
	s3 = scan(subjects,3,"-");
	if s2 in (&list) then subjects=catx("-",s1,s3);
run;

*or if you like a macro...;

%macro fix_subj(dsin=,dsout=,subjvar=,mylist=);
	data &dsout(drop=s1 s2 s3);
		set &dsin;
		s1 = scan(&subjvar,1,"-");
		s2 = scan(&subjvar,2,"-");
		s3 = scan(&subjvar,3,"-");
		if s2 in (&mylist) then &subjvar=catx("-",s1,s3);
	run;
%mend;

%fix_subj(dsin=have,dsout=want,subjvar=subjects,mylist=&list);

-unison

[Patrick]

Here a way using a regular expression

%let list = 001 002 003 004;

data have;
  input subjects $10.;
  cards;
SUBJ-001-M
SUBJ-002-J
SUBJ-005-K
SUBJ-007-K
SUBJ-004-N
;

data _null_;
  length listRegEx $100;
  listRegEx=cats('-',tranwrd(compbl("&list"),'','-|-'),'-');
  call symputx('listRegEx',listRegEx);
  stop;
run;

data want;
  set have;
  length subjects2 $10;
  subjects2=prxchange("s/&listRegEx/-/o",1,strip(subjects));
run;

proc print;
run;

[gsk]

Thank you so much! This is great. Don’t know why I can only accept one post as a solution 😞

[Ksharp]

%let list = 001 002 003 004;

data have;
input @1 subjects $10.;
cards;
SUBJ-001-M
SUBJ-002-J
SUBJ-005-K
SUBJ-007-K
SUBJ-004-N
;
run;

data want;
	set have;
	call  scan(subjects,2,p,l,"-");
	if findw("&list",scan(subjects,2,"-")) then 
subjects=cats(substr(subjects,1,p-1),substr(subjects,p+l+1));
run;