topic Re: Deriving variables based on pattern of variable values. in SAS Programming

Deriving variables based on pattern of variable values.

skom1 — Tue, 24 Dec 2019 21:14:48 GMT

Hello all,

Here is a sample data for which I need help with.

data tr1;

id$ datea dateb datec resa resb resc

001 21277 21278 21279 0 1 0

002 21238 21239 21240 1 2 0

003 21200 21201 21202 1 0 1

I want output data as

id$ max_res first_dt last_dt patt

001 1 21278 21278 S

002 2 21238 21239 C

003 1 21200 21202 I

I am new to sas programming and I am stuck with this case for a long time after trying it different ways which I have failed terribly) now I am posting it here.Please help me to resolve the case. Any help is much appreciated.

Thanks

Re: Deriving variables based on pattern of variable values.

PaigeMiller — Tue, 24 Dec 2019 21:21:44 GMT

Please explain the logic that let's you go from the input data to the output table.

Re: Deriving variables based on pattern of variable values.

skom1 — Tue, 24 Dec 2019 21:36:04 GMT

Sure. datea, dateb and datec are collection dates for results resa,resb and resc(a,b,c are suffixes which reperesnt different timepoints they are collected.). When res_ ne 0 then first_dt is the date at which first result is collected and if there is single result for that subject ,then patt is 's'. If there are results collected continuoulsy at every timepoint then last_dt is the date of last time point and patt='c'.

if results collected at differnt timepoints and are not continuous(suffixes a,b, c etc) then patt=I and last-dt=last time point results are collected.

Re: Deriving variables based on pattern of variable values.

novinosrin — Tue, 24 Dec 2019 22:08:05 GMT

data tr1;
input id$       datea         dateb      datec      resa      resb      resc;
cards;
001       21277       21278      21279      0            1          0
002       21238       21239      21240      1            2          0
003       21200       21201      21202      1            0          1
;

data want;
set tr1;
array t resa--resc;
array d datea--datec;
array temp(3)  _temporary_;
max_resc=max(of t(*));
call missing(of temp(*));
do over t;
 if t then temp(_i_)=d;
end;
firstdate=min(of temp(*));
lastdate=max(of temp(*));
keep id firstdate lastdate max_resc;
run;

Re: Deriving variables based on pattern of variable values.

skom1 — Tue, 24 Dec 2019 22:11:42 GMT

Thank You. But I see 'Patt' variable is not derived. could you please help me with that as well.
if single result, patt=S
if results are collected continuously then Patt=C
if results collected intermittently then Patt = 'I'.
Thank you

Re: Deriving variables based on pattern of variable values.

Astounding — Tue, 24 Dec 2019 22:28:38 GMT

I think there's yet more work necessary. I don't think the dates would come out properly yet.

Here's a similar approach for you to consider:

data want;
set have;
array res {3} resa resb resc;
array dts {3} datea dateb datec;
n_res=0;
do k=1 to 3;
   if res{k} then do;
      n_res + 1;
      first_dt = min(first_dt, dts{k});
      last_dt = max(last_dt, dts{k});
  end;
end;
maxres = max(resa, resb, resc);
if n_res=1 then patt='S';
else if n_res = 3 then patt='C';
else if n_res = 2 then do;
   if resb=0 then patt='I';
   else patt = 'C';
end;
run;

Re: Deriving variables based on pattern of variable values.

ballardw — Tue, 24 Dec 2019 22:59:09 GMT

@skom1 wrote:

Sure. datea, dateb and datec are collection dates for results resa,resb and resc(a,b,c are suffixes which reperesnt different timepoints they are collected.). When res_ ne 0 then first_dt is the date at which first result is collected and if there is single result for that subject ,then patt is 's'. If there are results collected continuoulsy at every timepoint then last_dt is the date of last time point and patt='c'.

if results collected at differnt timepoints and are not continuous(suffixes a,b, c etc) then patt=I and last-dt=last time point results are collected.

Is there a fixed number of these suffixes? Your statement above shows etc after c which sort of implies that there may be MANY of these. Your example data only shows a, b, and c but if there is a variable number then some approaches may not work.

You don't show dat with "continuously collected at every timepoint" if resc=0 is supposed to mean not collected. So you need to provide a more concise definition of the "C" conditions. And if you actually have more than 3 res variable what happens if you have the first and second with a value other than 0, the third has a 0 and the fourth and fifth have values other than 0?

Re: Deriving variables based on pattern of variable values.

novinosrin — Tue, 24 Dec 2019 23:04:04 GMT


data tr1;
input id$       datea         dateb      datec      resa      resb      resc;
cards;
001       21277       21278      21279      0            1          0
002       21238       21239      21240      1            2          0
003       21200       21201      21202      1            0          1
;

data want;
set tr1;
array t resa--resc;
array d datea--datec;
array temp(3)  _temporary_;
max_resc=max(of t(*));
call missing(of temp(*));
do over t;
 if t then do;
  temp(_i_)=d;
 if _t>. and  _i_-_t>1 then Patt = 'I';
 else patt='C';
 _t=_i_;
 end;
end;
if n(of temp(*))=1 then patt='S';
firstdate=min(of temp(*));
lastdate=max(of temp(*));
keep id firstdate lastdate max_resc patt;
run;

Re: Deriving variables based on pattern of variable values.

skom1 — Tue, 24 Dec 2019 23:21:27 GMT

Yes. there are more suffixes(a to h). "C' is only populated when results are collected at all successive timepoints. In the case you specified, if first, second results ne '0' and thirds=0 and 4th, 5th are ne '0' , patt = 'I'.

Re: Deriving variables based on pattern of variable values.

r_behata — Tue, 24 Dec 2019 23:46:10 GMT

data tr1;
input id$       datea         dateb      datec      resa      resb      resc;
cards;
001       21277       21278      21279      0            1          0
002       21238       21239      21240      1            2          0
003       21200       21201      21202      1            0          1
;
run;


data want;
set tr1;

array dt date:;
array re  res:;


do i=1 to dim(re);
if re[i] > 0 then do;
	firstdt=dt[i];
	ind1=i;
	leave;
end;
end;

do j=dim(re) to 1 by -1;
if re[j] > 0 then do;
	lastdt=dt[j];
	ind2=j;
	leave;
end;
end;

max_res=max(of re[*]);
 
if ind1=ind2 then	
	patt='S';
else if ind2=ind1+1 then
	patt='C';
else 	patt='I';

keep id max_res firstdt lastdt patt;
run;

Re: Deriving variables based on pattern of variable values.

mkeintz — Wed, 25 Dec 2019 04:24:22 GMT

You can make an array parallel to RESA,RESB,RESC in which each array element (call it _RES{i}) is missing whenever the analogous RES variable is zero, and is set to equal i otherwise. So for the third obs the _RES array would be {1,.,3}.

Then apply the MAX, MIN, and N functions to _RES to identify the element of the corresponding DATE array to use for first_dt, last_dt, and patt:

data have;
input id$       datea         dateb      datec      resa      resb      resc;
cards;
001       21277       21278      21279      0            1          0
002       21238       21239      21240      1            2          0
003       21200       21201      21202      1            0          1
;


data want (drop=i _r:);
  set have;
  array dat {*} date: ;
  array res {*} res:  ;

  array _res {3};   /*Gets a missing value when RES=0, or else the element index when RES^=0*/
  do i=1 to 3;
    if res{i}^=0 then _res{i}=i;
  end;

  first_dt=dat{min(of _res{*})};
  last_dt=dat{max(of _res{*})};

  if first_dt=last_dt then patt='S';
  else if max(of _res{*}) = min(of _res{*}) + n(of _res{*})-1 then patt='C';
  else patt='I';
run;

Re: Deriving variables based on pattern of variable values.

skom1 — Wed, 25 Dec 2019 20:25:36 GMT

Thank you for the reply. The dataset I am currently working on have 8 dates and result variables. When I am trying to use your code it says array is out of range at _res{i}. Can you help me debug. Thanks

Re: Deriving variables based on pattern of variable values.

mkeintz — Thu, 26 Dec 2019 04:21:06 GMT

Two comments:

You report an error message, but it is unaccompanied by a copy of the problem program, or the log containing the message. Help us help you by providing information needed to diagnose and prescribe.
HOWEVER ... you refer to having 8 RES (and DATE) variables, so that means your arrays (including _RES) should all have 8 elements. (My example defined _RES as having only 3). And any do loops from 1 to 3 should be changed to loop from 1 to 8.

Re: Deriving variables based on pattern of variable values.

skom1 — Thu, 26 Dec 2019 20:45:29 GMT

Hi,

here is the program and its error message.

26 data ZRS1 /*(drop=i _r:)*/;
27 set ZRS;
28 array dat {*} date: ;
29 array res {*} res: ;
30
31 array _res {8} ;
32 do i=1 to 8;
33 mindt = min(of dat[*]);
34 maxdt = max(of dat[*]);
35 if res{i}^='0' then _res{i}=i;
36 end;
37
38 if _res{i}=. then do;
39
40 first_dt =mindt;
41 last_dt = maxdt;
42 end;
43
44 else if _res{i} ne . then do;
45 first_dt=dat{min(of _res{*})};
46 last_dt=dat{max(of _res{*})};end;
47
48
49 if first_dt=last_dt then patt='S';
50 else if max(of _res{*}) = min(of _res{*}) + n(of _res{*})-1 then patt='C';
51 else patt='I';
52 run;

ERROR: Array subscript out of range at line 38 column 5.

Thank You.

Re: Deriving variables based on pattern of variable values.

mkeintz — Thu, 26 Dec 2019 20:56:05 GMT

I'm not sure how you got from my program to yours, but it is not close enough for me to give compact answers.

Strip your program back to mine. Make only the changes necessary to accommodate 8 RES values rather than the 3 in my suggestion. Then if you have a problem report back to us.

Re: Deriving variables based on pattern of variable values.

ballardw — Thu, 26 Dec 2019 21:54:12 GMT

At the end of an iterated do loop such as

do I= 1 to 8;

you find the value of I to be one more than the maximum value (unless you have some code to skip out of the loop early).

So in your code (slightly reformatted for legibility)

32 do i=1 to 8;
33    mindt = min(of dat[*]);
34    maxdt = max(of dat[*]);
35    if res{i}^='0' then _res{i}=i;
36 end;
37
38 if _res{i}=. then do;

line 38 uses the value of the index variable I after ending the loop and will have a value of 9. So the array index is out of range as you define the array _res to have 8 elements. Line 44 will have the same issue.

Note that you will get the exact same value for

33 mindt = min(of dat[*]); 
34 maxdt = max(of dat[*]);

So these really do not need to be in any sort of loop.

Re: Deriving variables based on pattern of variable values.

skom1 — Fri, 27 Dec 2019 23:43:12 GMT

Got it. Thank You very much