Solved: Re: Creating dummy variables

Jessica98 · Posted 11-16-2014 11:14 PM

Hi guys,

I need some help creating dummy variables for a dataset, can someone help with a simple SAS code? the data sample looks like this, i have given what i have and what i want as my output. I need monthly dummy's. please note that this is just a sample, i have data with many ID's, here i have given only one.

have						want
ID	year	month	months_continuous	bef_3months	aft_12_months	ID	year	month	months_continuous	bef_3months	aft_12_months	month_-3	month_-2	month_-1	month_+0	month_+1	month_+2	month_+3	month_+4	month_+5	month_+6	month_+7	month_+8	month_+9	month_+10	month_+11
68	2009	1	1	0	0	68	2009	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2009	2	2	0	0	68	2009	2	2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2009	3	3	0	0	68	2009	3	3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2009	4	4	0	0	68	2009	4	4	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2009	5	5	0	0	68	2009	5	5	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2009	6	6	0	0	68	2009	6	6	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2009	7	7	0	0	68	2009	7	7	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2009	8	8	1	0	68	2009	8	8	1	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2009	9	9	1	0	68	2009	9	9	1	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0
68	2009	10	10	1	0	68	2009	10	10	1	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0
68	2009	11	11	0	1	68	2009	11	11	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0
68	2010	1	13	0	1	68	2010	1	13	0	1	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0
68	2010	2	14	0	1	68	2010	2	14	0	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0
68	2010	3	15	0	1	68	2010	3	15	0	1	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0
68	2010	4	16	0	1	68	2010	4	16	0	1	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0
68	2010	5	17	0	1	68	2010	5	17	0	1	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0
68	2010	6	18	0	1	68	2010	6	18	0	1	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0
68	2010	7	19	0	1	68	2010	7	19	0	1	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0
68	2010	8	20	0	1	68	2010	8	20	0	1	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0
68	2010	9	21	0	1	68	2010	9	21	0	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0
68	2010	10	22	0	1	68	2010	10	22	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0
68	2010	11	23	0	1	68	2010	11	23	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1

-jessica wolfman

Ksharp · Posted 11-20-2014 07:38 AM

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

View solution in original post

Ksharp · Posted 11-17-2014 09:06 AM

What is your logic ?

Jessica98 · Posted 11-17-2014 09:15 AM

My logic is simple. I want a dummy for each month before three months(t-3) and after 12 months (0-11) of an individual s bday. I have the dummy indicating that. But I would need one dummy variable for each month. Basically for each individual has his bday month for example individual ID 68 has his bday in the 11th month 2009. , I want to create three dummy variable for three months before that (t-3,t-2,t-1) and 12 dummy variables(t0...t+11) representing each month after bday month.

RW9 · Posted 11-17-2014 09:46 AM

IMO you would be better off with a normalized table, i.e. instead of variables for each month gap, have a row. Alternatively if you must have variables, then call them all the same name, with and array statement, e.g. array months{10}. You can label each one separately so identifying them is easy, but you then have additional functionality when using that data, i.e. you can easily loop of it, pass the array into functions etc.

E.g. I want to use the data as you have given, thus in each datastep I need to do:

data xyz;

array values{10} month_minus_3 month_minus_2 month_minus_1 ...;

Using same identifier:

data xyz;

array values{10} month1-month10;

Jessica98 · Posted 11-18-2014 02:37 AM

i AM NEW TO SAS, I DONT HAVE A GOOD UNDERSTANDING OF ARRAYS. keshan, did you get my logic?

RW9 · Posted 11-18-2014 04:12 AM

No need to shout. I would suggest reading up on arrays, they are very useful when dealing with normalized (i.e. data which goes across the table) data.

http://www2.sas.com/proceedings/sugi30/242-30.pdf

Its not particularly difficult, think of an array as a number reference to a set of variables. Because its a numbered reference you can use the number to loop over lots of variables, or access specific ones.

Ksharp · Posted 11-18-2014 07:38 AM

OK. Still hard to catch on you. If I understood what you mean.

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
;
run;
data temp;
 set have;
 retain flag 1 n -4;
 if bef_3months = 1 or   aft_12_months = 1 then do;n+1;output;end;
run;
proc transpose data=temp out=temp1(drop=_:) prefix=mon_;
by      months_continuous ;
var flag;
id n;
run;
data temp2;
 merge have temp1  ;
 by  months_continuous;
run;
proc stdize data=temp2 missing=0 out=want reponly;run;

Xia Keshan

Jessica98 · Posted 11-18-2014 07:28 PM

hi , i ran the code.i think by looking at the code you got my logic. However i got this error " Data set WORK.TEMP is not sorted in ascending sequence. The current BY group has months_continuous = 23 and the next BY group has months_continuous= 3."

i tried sorting the temp data set by months_continuous, it did not help. any suggestions?

-jessica wolfman

Reeza · Posted 11-18-2014 09:57 PM

Show your code. You need to sort the input to the proc transpose and/or possibly your HAVE dataset if you haven't already.

Most likely this ahead of Xia's code should solve your problems:

proc sort data=have;

by id year month;

run;

Jessica98 · Posted 11-18-2014 10:48 PM

Hi, here is my code, I just want to say that the name of my 'ID' is 'driver_id' , will that make any difference? coz i dont see any ref to that in the code.

proc sort data=data;

by year month_continuous;

run;

data temp;

set data;

retain flag 1 n -4;

if before_3months = 1 or after_12months = 1 then do;n+1;output;end;

run;

proc transpose data=temp out=temp1(drop=_:) prefix=mon_;

by continuous ;

var flag;

id n;

run;

data temp2;

merge data temp1;

by month_continuous;

run;

proc stdize data=temp2 missing=0 out=want reponly;run;

RW9 · Posted 11-19-2014 04:07 AM

I agree with Xia Keshan, not following what your trying to do. If you just want a bunch of variables then the below code creates variables, you can name them what you want, but I would recommend that you have some logical order so that if you want to work with these, they can be set as an array again, or referred to in shorthand mnth1-mnth13 etc.

data want;

set have;

array mnth{13} 3.;

label mnth1 "3 months before"

mnth2 "2 months before"

mnth3 "1 month before"

mnth4 "1 month after"; /* ... */

run;

Ksharp · Posted 11-19-2014 07:36 AM

As Reeza pointed out. You 'd better post your real data.

If I guess right the ID variable have multiple values .

Code updated.

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
69     2009     1     1     0     0
69     2009     2     2     0     0
69     2009     3     3     0     0
69     2009     4     4     0     0
69     2009     5     5     0     0
69     2009     6     6     0     0
69     2009     7     7     0     0
69     2009     8     8     1     0
69     2009     9     9     1     0
69     2009     10     10     1     0
69     2009     11     11     0     1
69     2010     1     13     0     1
69     2010     2     14     0     1
69     2010     3     15     0     1
69     2010     4     16     0     1
69     2010     5     17     0     1
69     2010     6     18     0     1
69     2010     7     19     0     1
69     2010     8     20     0     1
69     2010     9     21     0     1
69     2010     10     22     0     1
69     2010     11     23     0     1
;
run;

proc sort data=have; by id      months_continuous;run;
data temp;
 set have;
 by id;
 retain flag 1 n -4;
 if first.id then n=-4;
 if bef_3months = 1 or   aft_12_months = 1 then do;n+1;output;end;
run;

proc transpose data=temp out=temp1(drop=_:) prefix=mon_;
by   id  months_continuous ;
var flag;
id n;
run;
data temp2;
 merge have temp1  ;
 by id months_continuous;
run;
proc stdize data=temp2 missing=0 out=want reponly;run;

Xia Keshan

Reeza · Posted 11-19-2014 01:55 PM

At the least your proc sort needs to include ID, or whatever your ID is truly called.

proc sort data=data;

by ID year month_continuous;

run;

Ksharp · Posted 11-19-2014 07:50 AM

OR if bef_3months aft_12_months is dynamic . Try this one.

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
69     2009     1     1     0     0
69     2009     2     2     0     0
69     2009     3     3     0     0
69     2009     4     4     0     0
69     2009     5     5     0     0
69     2009     6     6     0     0
69     2009     7     7     0     0
69     2009     8     8     0     0
69     2009     9     9     1     0
69     2009     10     10     1     0
69     2009     11     11     0     1
69     2010     1     13     0     1
69     2010     2     14     0     1
69     2010     3     15     0     1
69     2010     4     16     0     1
69     2010     5     17     0     1
69     2010     6     18     0     1
69     2010     7     19     0     1
69     2010     8     20     0     1
69     2010     9     21     0     1
69     2010     10     22     0     1
69     2010     11     23     0     1
;
run;
proc sql;
create table x as
 select *,-1*(sum(bef_3months)+1) as sum
  from have
   group by id
    order by id,months_continuous;
quit;

data temp;
 set x;
 by id;
 retain flag 1 n;
 if first.id then n=sum;
 if bef_3months = 1 or   aft_12_months = 1 then do;n+1;output;end;
run;

proc transpose data=temp out=temp1(drop=_:) prefix=mon_;
by   id  months_continuous ;
var flag;
id n;
run;
data temp2;
 merge have temp1  ;
 by id months_continuous;
run;
proc stdize data=temp2 missing=0 out=want reponly;run;

Xia Keshan

Jessica98 · Posted 11-19-2014 07:47 PM

Hi , thanks a lot! So far i am getting three columns with dummy's indicating bef_3months like this,

id	year	month	months_continuous	bef_3months	aft_12months	mon__3	mon__2	mon__1
68	2009	1	1	0	0	0	0	0
68	2009	2	2	0	0	0	0	0
68	2009	3	3	0	0	0	0	0
68	2009	4	4	0	0	0	0	0
68	2009	5	5	0	0	0	0	0
68	2009	6	6	0	0	0	0	0
68	2009	7	7	0	0	0	0	0
68	2009	8	8	1	0	1	0	0
68	2009	9	9	1	0	0	1	0
68	2009	10	10	1	0	0	0	1
68	2009	11	11	0	1	0	0	0
68	2010	1	13	0	1	0	0	0
68	2010	2	14	0	1	0	0	0
68	2010	3	15	0	1	0	0	0
68	2010	4	16	0	1	0	0	0
68	2010	5	17	0	1	0	0	0
68	2010	6	18	0	1	0	0	0
68	2010	7	19	0	1	0	0	0
68	2010	8	20	0	1	0	0	0
68	2010	9	21	0	1	0	0	0
68	2010	10	22	0	1	0	0	0
68	2010	11	23	0	1	0	0	0
72	2009	1	1	0	0	0	0	0
72	2009	2	2	0	0	0	0	0
72	2009	3	3	1	0	1	0	0
72	2009	4	4	1	0	0	1	0
72	2009	5	5	1	0	0	0	1
72	2009	6	6	0	1	0	0	0
72	2009	7	7	0	1	0	0	0
72	2009	8	8	0	1	0	0	0
72	2009	9	9	0	1	0	0	0
72	2009	10	10	0	1	0	0	0
72	2009	11	11	0	1	0	0	0
72	2010	1	13	0	1	0	0	0
72	2010	2	14	0	1	0	0	0
72	2010	3	15	0	1	0	0	0
72	2010	4	16	0	1	0	0	0
72	2010	5	17	0	1	0	0	0
72	2010	6	18	0	1	0	0	0

what i want is similar dummy's for aft_months as well (see below), here i need twelve variables similar to the three generated earlier. Like this, can i change the code to get that?

id	year	month	months_continuous	bef_3months	aft_12months	mon__3	mon__2	mon__1	mon__2	mon__3	mon__4	mon__5	mon__6	mon__7	mon__8	mon__9	mon__10	mon__11	mon__12	mon__13
68	2009	1	1	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
68	2009	3	3	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
68	2009	5	5	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
68	2009	7	7	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
68	2009	9	9	1	0	0	1	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
68	2009	11	11	0	1	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0
68	2010	1	13	0	1	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0
68	2010	2	14	0	1	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0
68	2010	3	15	0	1	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0
68	2010	4	16	0	1	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0
68	2010	5	17	0	1	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0
68	2010	6	18	0	1	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0
68	2010	7	19	0	1	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0
68	2010	8	20	0	1	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0
68	2010	9	21	0	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0
68	2010	10	22	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0
68	2010	11	23	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1
72	2009	1	1	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
72	2009	3	3	1	0	1	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
72	2009	5	5	1	0	0	0	1	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
72	2009	7	7	0	1	0	0	0	0	1	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
72	2009	9	9	0	1	0	0	0	0	0	0	1	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
72	2009	11	11	0	1	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0
72	2010	1	13	0	1	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0
72	2010	2	14	0	1	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0
72	2010	3	15	0	1	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0
72	2010	4	16	0	1	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0
72	2010	5	17	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0
72	2010	6	18	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1

SAS Innovate 2025: Register Now

SAS Training: Just a Click Away