topic Re: Create a new variable from three other variables in SAS Programming

Create a new variable from three other variables

Anni2017 — Sun, 14 May 2017 17:59:58 GMT

Hej SAS Experts, I hope someone can help me on this problem... I use SAS 9.4

I want to create a new variable from other variables in an existing data-set.

In this data-set I have three IDs, the ID of an individual, and its parents (father, mother). The problem is, the data-set gives no information about the sex of my individual, but the individual appears again e.g. as a father. I need to check the sex of my individuals...

If I have an example data-set like this:

ID_I ID_F ID_M

11 21 31

12 22 32

13 23 33

14 11 12

15 11 13

16 11 12

17 14 12

18 14 15

19 14 16

What I want to achieve with a code is an additional variable (1 for Mother, 2 for Father and 0 for unknown):

ID_I ID_F ID_M Sex

11 21 31 2

12 22 32 1

13 23 33 1

14 11 12 2

15 11 13 1

16 11 12 1

17 14 12 0

18 14 15 0

19 14 16 0

Does SAS have any function which can do this?

My idea was to do it with the IN() Function but then I would need to have arrays not variables? Anyway this was my failed try:

Data want;

Set Data Have;

Sex= ID_I in ID_F;

put Sex=2;

Sex=ID_I in ID_D;

put Sex=1;

RUN;

I have more than 10.000 observations for every variable, so I don´t know if it is possible to create arrays from my variables? Is there any other smart solution to this?

I would appreciate your help a lot, I am still new to SAS and this is just too complicated for me.

Thank you in advance Anni

Re: Create a new variable from three other variables

art297 — Sun, 14 May 2017 22:32:30 GMT

10,000 is really small for a SAS array. You can use them for many millions.

If I correctly understand what you're trying to do, try the following:

data want (drop=n);
 array fathers(100000) _temporary_;
 array mothers(100000) _temporary_;
 do until (last);
 set have end=last;
 n+1;
 fathers(n)=ID_F;
 mothers(n)=ID_M;
 end;
 do until (last2);
 set have end=last2;
 if id_i in fathers then sex=2;
 else if id_i in mothers then sex=1;
 else sex=0;
 output;
 end;
run;

Art, CEO, AnalystFinder.com

Re: Create a new variable from three other variables

Reeza — Sun, 14 May 2017 19:02:12 GMT

This is a bit of a longer approach, but more introductory level solution that you should be able to modify as necessary.

1. Create sample data - not required in your actual code. Use the data you already have.

2. Create master list of gender data from original data by appending the two datasets and assigning F/M as indicated.

3. Ensure list above has one record per ID and gender - see comments in code below.

4. Merge results back in and keep only records from the original file.

*input sample data;

data have;
	input ID_I ID_F ID_M;
	cards;
11      21      31     
12      22      32    
13      23      33  
14      11      12      
15      11      13
16      11      12
17      14      12
18      14      15
19      14      16
;
run;

*Get gender from file originally - should make sure this is unique
one record per ID;

data gender;
	set have (rename=ID_F=ID in=Males) have (rename=ID_M=ID in=Females);

	if Males then
		sex=2;

	if Females then
		sex=1;
	keep ID SEX;
run;

*Sort for merging;

proc sort data=gender nodupkey;
	by id sex;
run;

*CHECK FOR duplicates - if the log shows duplicates you have a problem with your data
mainly ID identified as both male and female; 
proc sort data=gender out=checkme nodupkey;
by id;
run;

proc sort data=have;
	by ID_I;
run;


*merge data;

data want;
	merge have (in=original) gender (rename=ID=ID_I);
	by ID_I;

	if original;
run;

Re: Create a new variable from three other variables

Shmuel — Sun, 14 May 2017 20:45:55 GMT

Another solution similar logic to @Reeza's, but with a format:

/* step 1 - create a format */

data ID_sex(rename=(sex=label));

set have;

retain fmtname 'sexf';

start = id_f; sex='2'; output;

start = id_m; sex='1'; output;

keep fmtname start sex;

run;

proc sort data=id_sex out=ID_sex nodupkey; by start; run;

proc format cntlin=ID_sex; run;

data want;

set have;

length sex $1;

sex = put(id_i,sexf.);

run;

Re: Create a new variable from three other variables

mkeintz — Sun, 14 May 2017 22:17:18 GMT

@art297

The "do until (last);" won't increment _N_, so you need to change the code inside the loop.

Mark

Re: Create a new variable from three other variables

art297 — Sun, 14 May 2017 22:33:02 GMT

I revised my code based on @mkeintz's comment. He was totally correct and here is the new code:

data have;
  input id_I ID_F ID_M;
  cards;
11      21      31     
12      22      32    
13      23      33  
14      11      12      
15      11      13
16      11      12
17      14      12
18      14      15
19      14      16
;

data want (drop=n);
  array fathers(100000) _temporary_;
  array mothers(100000) _temporary_;
  do until (last);
    set have end=last;
    n+1;
    fathers(n)=ID_F;
    mothers(n)=ID_M;
  end;
  do until (last2);
    set have end=last2;
    if id_i in fathers then sex=2;
    else if id_i in mothers then sex=1;
    else sex=0;
    output;
  end;
run;

Re: Create a new variable from three other variables

novinosrin — Mon, 15 May 2017 07:33:11 GMT

Another easy solution:

data have;

input ID_I ID_F ID_M;

cards;

11 21 31

12 22 32

13 23 33

14 11 12

15 11 13

16 11 12

17 14 12

18 14 15

19 14 16

;

run;

data want;

if _N_ = 1 then do;

if 0 then set have;

declare hash myhash(dataset:'have(keep=ID_F)',multidata:'YES');

myhash.defineKey('ID_F');

myhash.defineDone( );

declare hash myhash2(dataset:'have(keep=ID_M)',multidata:'YES');

myhash2.defineKey('ID_M');

myhash2.defineDone( );

end;

set have;

if myhash.check(key:ID_I)=0 then Sex=2;

else if myhash2.check(key:ID_I)=0 then Sex=1;

else Sex=0;

run;

Regards,

Naveen Srinivasan

Re: Create a new variable from three other variables

Anni2017 — Mon, 15 May 2017 11:36:36 GMT

Dear Art, yes that is exactly what I wanted to do.

The results seem to be correct. Thank you very much for your help!!

Kindest regards Anni

Re: Create a new variable from three other variables

Anni2017 — Mon, 15 May 2017 11:38:38 GMT

Thank you Mark the code first gave the wrong result, but now it works perfectly!!

Anni

Re: Create a new variable from three other variables

Ksharp — Mon, 15 May 2017 12:42:38 GMT

I should not jump into this hive.
Since I have some time, I would like to introduce SQL solution.



data have;
	input ID_I ID_F ID_M;
	cards;
11      21      31     
12      22      32    
13      23      33  
14      11      12      
15      11      13
16      11      12
17      14      12
18      14      15
19      14      16
;
run;

proc sql;
create table want as
 select *,case when id_i in (select id_f from have) then 2
               when id_i in (select id_m from have) then 1
               else 0 end as sex 
  from have;
quit;

Re: Create a new variable from three other variables

art297 — Mon, 15 May 2017 15:01:48 GMT

@Ksharp: What hive? Alternative and potentially better solutions, in my opinion, are always welcome. And, in this case, using sql subquerries, is far more efficient than the data step/array solution I had suggested.

@Anni2017: I would change the accepted solution to @Ksharp's solution. While both work, the SQL approach is far more efficient and will work with much larger data sets.

Anyone who might be interested: The main reason sql out performed the data step approach was that the size declared for the two arrays has a big impact on performance. That can be adjusted by first running a sql step to create macro variables containing the needed array sizes. I'd love to compare the three approaches on an actual large file, but did a pseudo comparison by just expanding the size of the example data.

In my example, the revised data step approach out performed the sql approach, and the sql approach out performed the hash approach:

data have;
	input ID_I ID_F ID_M;
	do i=1 to 500000;
	  output;
	end;
	cards;
11      21      31     
12      22      32    
13      23      33  
14      11      12      
15      11      13
16      11      12
17      14      12
18      14      15
19      14      16
;
run;

proc sql noprint;
  select count(distinct id_m),count(distinct id_f)
    into :m_count, :f_count
      from have
  ;
quit;

data want1 (drop=n:);
  array fathers(&f_count) _temporary_;
  array mothers(&m_count) _temporary_;
  do until (last);
    set have end=last;
    if ID_F not in fathers then do;
      nF+1;
      fathers(nF)=ID_F;
    end;
    if ID_M not in mothers then do;
      nM+1;
      mothers(nM)=ID_M;
    end;
  end;
  do until (last2);
    set have end=last2;
    if id_i in fathers then sex=2;
    else if id_i in mothers then sex=1;
    else sex=0;
    output;
  end;
run;

proc sql;
create table want2 as
 select *,case when id_i in (select id_f from have) then 2
               when id_i in (select id_m from have) then 1
               else 0 end as sex 
  from have;
quit;

data want3;
if _N_ = 1 then do;
     if 0 then set have;
     declare hash myhash(dataset:'have(keep=ID_F)',multidata:'YES');
      myhash.defineKey('ID_F');
      myhash.defineDone( );
     declare hash myhash2(dataset:'have(keep=ID_M)',multidata:'YES');
      myhash2.defineKey('ID_M');
      myhash2.defineDone( );
end;
set have;
if myhash.check(key:ID_I)=0 then Sex=2;
else if myhash2.check(key:ID_I)=0 then Sex=1;
else Sex=0;
run;

Art, CEO, AnalystFinder.com

Re: Create a new variable from three other variables

novinosrin — Mon, 15 May 2017 15:33:50 GMT

Thank you @art297 , @Ksharp for the very valuable insights and extending the knowledge for somebody like me to learn. I can't appreciate enough, not just this post, many of yours. I have shamelessly used your codes as a verbatim to many of my work. Thank you so much indeed.

Regards,

Naveen Srinivasan

Re: Create a new variable from three other variables

art297 — Mon, 15 May 2017 17:37:53 GMT

Update for anyone who might be interested: In the code, below, I tried to create a somewhat realistic looking fairly large (n=~100000 records) file. Using it, both the SQL and hash approaches were almost equivalent (about 0.1 seconds cpu time), while the datastep/array solution took almost 80 times longer to run (about 8 seconds) than either the SQL or hash approaches:

/* Create some sample data */
  /* SAS macro that duplicates the Excel RANDBETWEEN function */
  %macro RandBetween(min, max);
     (&min + floor((1+&max-&min)*rand("uniform")))
  %mend;

  data Mothers(drop=i rename=ID_F=ID_M) Fathers(drop=i);
    /* initialize random seed to ensure results are reproducible */
    call streaminit(123);
    do i = 1 to 100000;
      ID_F = %RandBetween(1, 50000);
      if mod(ID_F,2) then output Mothers;
      else output fathers;
    end;
  run;

  data mothers;
    set mothers;
    if _n_ le 49447;
  run;

  data have;
    call streaminit(123);
    set mothers mothers;
    set fathers fathers;
    array parents(*) ID_F ID_M;
    do until (ID_I not in parents);
      ID_I=%RandBetween(1, 50000);
    end;
  run;

proc sql noprint;
  select count(distinct id_m),count(distinct id_f)
    into :m_count, :f_count
      from have
  ;
quit;

data want1 (drop=n:);
  array fathers(&f_count) _temporary_;
  array mothers(&m_count) _temporary_;
  do until (last);
    set have end=last;
    if ID_F not in fathers then do;
      nF+1;
      fathers(nF)=ID_F;
    end;
    if ID_M not in mothers then do;
      nM+1;
      mothers(nM)=ID_M;
    end;
  end;
  do until (last2);
    set have end=last2;
    if id_i in fathers then sex=2;
    else if id_i in mothers then sex=1;
    else sex=0;
    output;
  end;
run;

proc sql;
create table want2 as
 select *,case when id_i in (select id_f from have) then 2
               when id_i in (select id_m from have) then 1
               else 0 end as sex 
  from have;
quit;

data want3;
  if _N_ = 1 then do;
     if 0 then set have;
     declare hash myhash(dataset:'have(keep=ID_F)',multidata:'YES');
      myhash.defineKey('ID_F');
      myhash.defineDone( );
     declare hash myhash2(dataset:'have(keep=ID_M)',multidata:'YES');
      myhash2.defineKey('ID_M');
      myhash2.defineDone( );
  end;
  set have;
  if myhash.check(key:ID_I)=0 then Sex=2;
  else if myhash2.check(key:ID_I)=0 then Sex=1;
  else Sex=0;
run;

Art, CEO, AnalystFinder.com

Re: Create a new variable from three other variables

Ksharp — Tue, 16 May 2017 12:12:12 GMT

Arthur.T ,
We are not competitor , we are partner .