SAS - creating indicator variables

Question

I'm using SAS and I'd like to create an indicator variable.

The data I have is like this (DATA I HAVE):

and I want to change this to (DATA I WANT):

I have a fixed number of total time that I want to use, and the starttime has duplicate time value (in this example, c1 and c2 both started at time 3). Although the example I'm using is small with 5 names and 12 time values, the actual data is very large (about 40,000 names and 100,000 time values - so the outcome I want is a matrix with 100,000x40,000.)

Can someone please provide any tips/solution on how to handle this?

Answer 1

I think the code below will work:

%macro answer_macro(data_in, data_out);

/* Deduplication of initial dataset just to assure that every variable has a unique starting time*/
proc sort data=&data_in. out=data_have_nodup; by name starttime; run;
proc sort data=data_have_nodup nodupkey; by name; run;

/*Getting min and max starttime values - here I am assuming that there is only integer values form starttime*/
proc sql noprint;
    select min(starttime)
            ,max(starttime) 
    into :min_starttime /*not used. Use this (and change the loop on the next dataset) to start the time variable from the value where the first variable starts*/
        ,:max_starttime
    from data_have_nodup
;quit;

/*Getting all pairs of name/starttime*/
proc sql noprint;
    select name
            ,starttime
    into :name1 - :name1000000
        ,:time1 - :time1000000
    from data_have_nodup
;quit;

/*Getting total number of variables*/
proc sql noprint;
    select count(*) into :nvars
    from data_have_nodup
;quit;

/* Creating dataset with possible start values */
/*I'm not sure this step could be done with a single datastep, but I don't have SAS 
on my PC to make tests, so I used the method below*/

data &data_out.;
    do i = 1 to &max_starttime. + 1;
        time = i; output;
    end;
    drop i;
run;

data &data_out.;
    set &data_out.;
    %do i = 1 %to &nvars.;
        if time >= &&time&i then &&name&i = 1;
        else &&name&i = 0;
    %end;
run;

%mend answer_macro;

Unfortunately I don't have SAS on my machine right now, so I can't confirm that the code works. But even if it doesn't, you can use the logic in it.

Answer 2

40k variables is a lot. It will be interesting to see how well this scales. How do you determine the stop time?

data have;
    input starttime name :$32.;
    retain one 1;
    cards;
1 varx
3 c1
3 c2
5 c3x
10 c4
11 c5
;;;;
   run;
proc print;
   run;
proc transpose data=have out=have2(drop=_name_ rename=(starttime=time));
   by starttime;
   id name;
   var one;
   run;
data time;
   if 0 then set have2(drop=time);
   array _n[*] _all_;
   retain _n 0;
   do time=.,1 to 12;
      output;
      call missing(of _n[*]);
      end;
   run;
data want0 / view=want0;
   merge time have2;
   by time;
   retain dummy '1';
   run;
data want;
   length time 8;
   update want0(obs=0) want0;
   by dummy;
   if not missing(time);
   output;
   drop dummy;
   run;
proc print;
   run;

Answer 3

This will work. There may be a simpler solution that does it all in one data step. My data step creates a staggered results that has to be collapsed which I do by summing in the sort/means.

data have;
    input starttime name $;
    datalines;
3 c1
3 c2
5 c3
10 c4
11 c5
;
run;

data want(drop=starttime name);
    set have;
    array cols (*) c1-c5;
    do time=1 to 100;
        if starttime < time then cols(_N_)=1;
        else cols(_N_)=0;
        output;
    end;
run;

proc sort data=want;
    by time;
proc means data=want noprint;
    by time;
    var _numeric_;
    output out=want2(drop=_type_ _freq_) sum=;
run;

I am not recommending you do it this way. You didn't provide enough information to let us know why you want a matrix of that size. You may have processing issues getting it to run.

In the line do time=1 to 100 you can change that to 100000 or whatever length.