topic Re: Compare similar names in one data in SAS Programming

Compare similar names in one data

Emma8 — Thu, 18 Jun 2020 12:55:14 GMT

Re: Compare similar names in one data

Jagadishkatam — Thu, 18 Jun 2020 06:41:25 GMT

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;

Re: Compare similar names in one data

Emma8 — Thu, 18 Jun 2020 06:47:10 GMT

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)?

Re: Compare similar names in one data

SASKiwi — Thu, 18 Jun 2020 07:13:34 GMT

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));

Re: Compare similar names in one data

yabwon — Thu, 18 Jun 2020 07:27:55 GMT

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

Re: Compare similar names in one data

Kurt_Bremser — Thu, 18 Jun 2020 08:02:48 GMT

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.

Re: Compare similar names in one data

Emma8 — Thu, 18 Jun 2020 18:56:14 GMT

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

Re: Compare similar names in one data

Kurt_Bremser — Thu, 18 Jun 2020 19:56:57 GMT

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.

Re: Compare similar names in one data

Emma8 — Fri, 19 Jun 2020 12:44:31 GMT

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

Re: Compare similar names in one data

Kurt_Bremser — Fri, 19 Jun 2020 13:11:54 GMT

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.

Re: Compare similar names in one data

Emma8 — Fri, 19 Jun 2020 13:40:52 GMT

Sorry, almost 30 000 obs and 1200 variables

Re: Compare similar names in one data

Kurt_Bremser — Fri, 19 Jun 2020 13:55:37 GMT

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

Re: Compare similar names in one data

Emma8 — Fri, 19 Jun 2020 14:09:51 GMT

Yeah, I do not have enough memory