topic Re: Comparing vertical data in New SAS User

Comparing vertical data

annaxenia1 — Sun, 27 Oct 2019 09:25:56 GMT

Hey everyone,

I have a problem with the a dataset like the following table.

year

modal

111

112

113

2006

123

112

113

114

115

2007

123

114

115

116

2008

114

2009

123

I want to find out which modal exists for each id and year.

The following combinations are possible: 1; 2; 3; 12; 13; 23 and 123.

What the output should look like:

year

Modal_total

111

112

113

112

113

114

115

114

115

116

114

2006

2007

2008

2009

123

Thanks a lot in advance. 🙂

Re: Comparing vertical data

PeterClemmensen — Sun, 27 Oct 2019 10:02:11 GMT

@annaxenia1 Hi and welcome to the SAS Community 🙂

Can you be more specific about the logic here? How exactly is Modal_total calculated?

Re: Comparing vertical data

annaxenia1 — Sun, 27 Oct 2019 10:20:49 GMT

Hi draycut and thanks for the quik reply:)

If you look at id 116 in 2008, the modal 2 occurs three times and the modal 3 occurs once. If a modal occurs one or more times doesn`t matter. For me it is interesting which numbers occur at all. In this case the modal_total should be 23 because 2 and 3 occurs.

Re: Comparing vertical data

Kurt_Bremser — Sun, 27 Oct 2019 11:25:33 GMT

Is the variable modal stored as character or numeric?

Re: Comparing vertical data

annaxenia1 — Sun, 27 Oct 2019 11:53:35 GMT

as numeric

Re: Comparing vertical data

Ksharp — Sun, 27 Oct 2019 11:58:09 GMT

data have;
input id
year
modal $
;
cards;
111   2006    1               
111   2006    2               
111   2006    123               
112   2006    13               
112   2006    12               
113   2006    12               
113   2006    2               
113   2006    2               
;
data temp;
 set have;
 do i=1 to length(modal);
  temp=char(modal,i);output;
 end;
 drop i modal;
run;
proc sort data=temp nodupkey;
by id year temp;
run;
data want;
 do until(last.year);
   set temp;
   by id year;
   length want $ 80;
   want=cats(want,temp);
 end;
 drop temp;
run;

Re: Comparing vertical data

FreelanceReinh — Sun, 27 Oct 2019 13:22:01 GMT

Hello @annaxenia1,

Here's another approach, assuming that your input dataset ("HAVE") is sorted by year id.

data want(drop=_: modal);
length _c $3;
do until(last.id);
  set have;
  by year id;
  if modal in (1,12,13,123) then substr(_c,1,1)='1';
  if modal in (2,12,23,123) then substr(_c,2,1)='2';
  if modal in (3,13,23,123) then substr(_c,3,1)='3';
end;
Modal_total=compress(_c);
run;

This creates Modal_total as a character variable (of length 3). If you prefer a numeric variable, please replace the assignment statement by

Modal_total=input(compress(_c),3.);

For input datasets with more than three different values (1, 2, 3) the lists in the IF conditions might become unwieldy. In this case I'd rather convert the modal values to character and apply a suitable character function.

Edit: Example of a variant using a character function (FINDC), also using an array instead of a character variable _c:

data want(drop=_: modal);
array _c[3] $1;
do until(last.id);
  set have;
  by year id;
  _m=put(modal,3.);
  do _i='1', '2', '3';
    if findc(_m,_i) then _c[input(_i,1.)]=_i;
  end;
end;
length Modal_total $3;
Modal_total=cats(of _c[*]);
run;

Re: Comparing vertical data

novinosrin — Sun, 27 Oct 2019 13:44:36 GMT

Hi @annaxenia1 My share of fun. I must admit, I kinda copied the idea from @Ksharp . Sorry Xia!

data have;
input id year modal ;
cards;
111	2006	1
111	2006	2
111	2006	123
112	2006	13
112	2006	12
113	2006	12
113	2006	2
113	2006	2
112	2007	2
112	2007	2
112	2007	123
112	2007	23
112	2007	23
113	2007	23
113	2007	3
113	2007	3
114	2007	3
114	2007	1
115	2007	1
115	2007	1
114	2008	12
114	2008	1
115	2008	13
115	2008	1
116	2008	2
116	2008	2
116	2008	2
116	2008	3
114	2009	3
114	2009	123
114	2009	1
114	2009	2
;

proc sort data=have out=_have;
by id year;
run;

data want;
 do until(last.year);
  set _have;
  by id year;
  array t(99999)$ _temporary_ ;
  do _n_=1 to length(cats(modal));
   temp=char(cats(modal),_n_);
   if temp not in t then t(temp)=temp;
  end;
 end;
 Modal_total=cats(of t(*));
 call missing(of t(*));
 drop temp modal;
run;