topic Re: categorical data management in SAS Data Management

categorical data management

echo991 — Thu, 14 Jan 2016 00:11:49 GMT

Hi,

I got a question about below problem:

I have some records for every person's job per month. If he/she complete, that month will be 1, otherwise 0. The form like below:

Name	Jan	Feb	Mar	Apr	May	June	July	Aug	Sep	Oct	Nov	Dec
Amy	1	0	0	0	0	1	0	0	1	0	0	1
David	1	1	1	1	1	0	0	1	0	1	0	0
John	0	0	1	0	0	0	1	1	1	1	1	1

The question is: How to find person that doesn't complete job more than 3 continuous months (contains 3) in one record? For example, Amy has 0 from Feb to May, and John has 0 from Apr to June, Amy and John should be extract.

Anyone has some ideas?

Many thanks,

Chen

Re: categorical data management

Reeza — Thu, 14 Jan 2016 00:21:27 GMT

Concatenate all the months into one string variable and search for the string 111. If you find at least that, then you know they've done 3 consecutive months.

Look at CATS/CATT functions for the concatenation and index or find for searching for the string 111

Re: categorical data management

ballardw — Thu, 14 Jan 2016 00:23:50 GMT

By extract do you mean that the result of this operation is a data set with those rows of data? If so this may get you started:

/* example data to work with*/
data have;
   input Name $	Jan	Feb	Mar	Apr	May	June	July	Aug	Sep	Oct	Nov	Dec ;
datalines;
Amy	1	0	0	0	0	1	0	0	1	0	0	1
David	1	1	1	1	1	0	0	1	0	1	0	0
John	0	0	1	0	0	0	1	1	1	1	1	1
;
run;



data want;
   set have;
   array m Jan  Feb Mar Apr May June July Aug Sep Oct Nov Dec;
   do i= 1 to (dim(m) -2);
      if sum(m[i],m[i+1],m[i+2])=0 then leave;
      else if i=10 then delete;
   end;
   drop i;
run;

BUT you will need a completely different approach if you ever want to look across the year boundary such as Dec 2014, Jan 2015, Feb 2015.

Re: categorical data management

FreelanceReinh — Thu, 14 Jan 2016 11:16:02 GMT

I support @Reeza's suggestion (with the obvious modification to search for '000'), although @ballardw's is fine, too. The selection (based on a HAVE dataset like that created by ballardw) could be as short as this:

data want;
set have;
if find(cats(of Jan--Dec),'000');
run;

Re: categorical data management

echo991 — Fri, 15 Jan 2016 14:58:33 GMT

It works!! Thanks so much Reeza.

Re: categorical data management

echo991 — Fri, 15 Jan 2016 14:59:06 GMT

Yes, it works!!! Thanks so much!