Compare similar names in one data

[Jagadishkatam]

You can try something as below

data want;
set have;
flag_age=(a_age=b_age);
flag_name=(m_name=l_name=c_name);
run;

[Emma8]

I do not want to write for each variables since there are many—any automated version (for example, search same suffix variables and create the flags)?

[SASKiwi]

For numeric variables something like this might work, assuming you have number suffixes on your variables:

Same_Age_Flag = (min(of age1-age10) = max(of age1 - age10));

[yabwon · Accepted solution]

Hi,

try this:

data have;
input a_age b_age m_name $ l_name $ c_name $;
cards;
1 1 A A A
1 0 A B C
;
run;

%macro getList(ds, sufix, separator=%str( ));
  %local variables rc;
  %let rc = %sysfunc(DoSubL(%str(
   options nonotes nosource nomprint nosymbolgen nomlogic;
  proc transpose 
    data = &ds.(obs=0) 
    out = _(where=(_Name_ like '%'||"&sufix." escape "\"));
  var _all_;
  run;
  proc sql noprint;
    select _name_ into :variables separated by "&separator." from _;
    drop table _; 
  run;
  )));
&variables.
%mend getList;

%put *%getList(have, \_name,separator==)*; 

options symbolgen mlogic;
  data want;
  set have;
  flag_age=(%getList(have, \_age,separator==));
  flag_name=(%getList(have, \_name,separator==));
run;
proc print;
run;

All the best

Bart

[Kurt_Bremser]

Solving issues like this becomes very easy with a long dataset structure, as this enables you to write the code without referencing variables literally:

data have;
input a_age b_age m_name $ l_name $ c_name $;
id = _n_; /* create an ID */
cards;
1 1 A A A
1 0 A B C
;

/* get a list of all variables except the ID */
proc sql noprint;
select name into :vars separated by " "
from dictionary.columns
where libname = 'WORK' and memname = 'HAVE' and upcase(name) ne 'ID';
quit;

proc transpose data=have out=trans (rename=(col1=value));
by id;
var &vars;
run;

data long;
set trans;
name = scan(_name_,-1,'_');
drop _name_;
run;

/* the following is ALL the code you need with a long structure */
proc sql;
create table want as
  select
    id,
    name,
    (count(distinct value) = 1) as flag
  from long
  group by id, name
;
quit;

See Maxim 19: Long Beats Wide.

[Emma8]

Thank you. The data is very large (over 10000 obs and about 700 variables ) —so anyway that without transpose the data ?

[Kurt_Bremser]

The more variables, the more reasons to transpose. Wide datasets are design problems in most cases. You have data in structure.

Even with all variables transposed, you end up with 7 million obs and 3 variables. That's not "large". Large starts at least 2 orders of magnitude above that.

Look at my final step again and think about it. 

[Emma8]

Sas could not transpose (warning message displayed as out of resource )—try to create over 30 000 000 000

[Kurt_Bremser]

A transpose of 700 variables out of 10000 observations can NOT end up with more than 7 million observations.

Did you use an id variable that identifies each individual observation?

Please post the whole log of the transpose step.

[Emma8]

Sorry, almost 30 000 obs and 1200 variables

[Kurt_Bremser]

So you get 36 million observations. Where is the problem?

[Emma8]

Yeah, I do not have enough memory