Creating dummy variables

[Ksharp · Accepted solution]

Sorry. OK .try this one.

Still hard.

data have;
input ID     year     month     months_continuous     bef_3months     aft_12_months;
cards;
68     2009     1     1     0     0
68     2009     2     2     0     0
68     2009     3     3     0     0
68     2009     4     4     0     0
68     2009     5     5     0     0
68     2009     6     6     0     0
68     2009     7     7     0     0
68     2009     8     8     1     0
68     2009     9     9     1     0
68     2009     10     10     1     0
68     2009     11     11     0     1
68     2010     1     13     0     1
68     2010     2     14     0     1
68     2010     3     15     0     1
68     2010     4     16     0     1
68     2010     5     17     0     1
68     2010     6     18     0     1
68     2010     7     19     0     1
68     2010     8     20     0     1
68     2010     9     21     0     1
68     2010     10     22     0     1
68     2010     11     23     0     1
72     2009     1     1     0     0
72     2009     2     2     0     0
72     2009     3     3     1     0
72     2009     4     4     1     0
72     2009     5     5     1     0
72     2009     6     6     0     1
72     2009     7     7     0     1
72     2009     8     8     0     1
72     2009     9     9     0     1
72     2009     10     10     0     1
72     2009     11     11     0     1
72     2010     1     13     0     1
72     2010     2     14     0     1
72     2010     3     15     0     1
72     2010     4     16     0     1
72     2010     5     17     0     1
72     2010     6     18     0     1
;
run;
proc sort data=have;by  id      months_continuous;run;
data x1(keep=id month rename=(month=_month));
 set have;
 if  bef_3months=0 and lag(bef_3months)=1;
run;
data x2;
 merge have x1;
 by id;
run;
data temp;
 set x2;
 by id;
 retain flag 1;
 if bef_3months = 1 or  aft_12_months = 1 then do;n=months_continuous-_month;output;end;
 drop _month;
run;

proc transpose data=temp out=temp1(drop=_:) prefix=mon;
by   id  months_continuous ;
var flag;
id n;
idlabel n;
run;
data temp2;
 merge have temp1  ;
 by id months_continuous;
run;
proc stdize data=temp2 missing=0 out=want reponly;run;
proc sql noprint;
select translate(cats('mon',label),'_','-') into : list separated by ' '
 from dictionary.columns
  where libname='WORK' and memname='WANT' and label is not missing
   order by input(strip(label),best32.);
quit;

data want;
 retain ID     year     month     months_continuous     bef_3months     aft_12_months
 &list ;
 set want;
run;

Xia Keshan

Message was edited by: xia keshan

Message was edited by: xia keshan Still hard

[Jessica98]

Hi ,

the code works brilliantly, i am trying to learn what you have done. I notice there is one small error, let me know what you think about it. For the output that i have obtained below, for ID 68 and & 72 it is "0" for mon1 & mon6 (underlined below) where it should have been "1" . There is one instance like this for all ID's. Any idea why?

ID	year	month	months_continuous	bef_3months	aft_12_months	mon_3	mon_2	mon_1	mon0	mon1	mon2	mon3	mon4	mon5	mon6	mon7	mon8	mon9	mon10	mon11	mon12	mon13	mon14	mon15	mon16	mon17	mon18	mon19	mon20	mon21	mon22	mon23
68	2009	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2009	2	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2009	3	3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2009	4	4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2009	5	5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2009	6	6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2009	7	7	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2009	8	8	1	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2009	9	9	1	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2009	10	10	1	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2009	11	11	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2010	1	13	0	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2010	2	14	0	1	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2010	3	15	0	1	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2010	4	16	0	1	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2010	5	17	0	1	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2010	6	18	0	1	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2010	7	19	0	1	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2010	8	20	0	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2010	9	21	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2010	10	22	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0
68	2010	11	23	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0
72	2009	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
72	2009	2	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
72	2009	3	3	1	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
72	2009	4	4	1	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
72	2009	5	5	1	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
72	2009	6	6	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
72	2009	7	7	0	1	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
72	2009	8	8	0	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
72	2009	9	9	0	1	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
72	2009	10	10	0	1	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
72	2009	11	11	0	1	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
72	2010	1	13	0	1	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
72	2010	2	14	0	1	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
72	2010	3	15	0	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
72	2010	4	16	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0
72	2010	5	17	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0
72	2010	6	18	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0

[Ksharp]

No . it is not error.

Because  that obs's months_continuous is missing . It should be 12 .So for id=72.

And glad it is working .

Xia Keshan