topic Re: How to make an snapshot of a date for each respondent in SAS Programming

How to make an snapshot of a date for each respondent

mandan414 — Thu, 12 Nov 2020 16:06:45 GMT

Hi everybody,

I have a dataset of students in a high school. The data includes the start-date and end-date for each student. I would like to make an snapshot of a date, e.g. 31 december of each year, and find out if the respondent attended in the high school on that date.

student start_date end_date

1 04 Sept, 1993 26 june, 1995

2 22 Nov, 2002 20 May, 2005

......

I would like to make a variable which is if the student 1 attended the high school at 31 december of each year between 1993 to 1995, then the value of the new variable is 1 otherwise 0, and the same for each pther student.

Thank you!

Re: How to make an snapshot of a date for each respondent

PaigeMiller — Thu, 12 Nov 2020 16:08:24 GMT

@mandan414 wrote:

student        start_date      end_date

1                 04 Sept, 1993         26 june, 1995

2                 22 Nov, 2002          20 May, 2005

Are these true numeric variables that are SAS date values, formatted? Or are they text strings?

Re: How to make an snapshot of a date for each respondent

mandan414 — Thu, 12 Nov 2020 16:13:54 GMT

They are the true values of the SAS, numeric, date ......

Re: How to make an snapshot of a date for each respondent

PaigeMiller — Thu, 12 Nov 2020 16:16:34 GMT

@mandan414 wrote:

They are the true values of the SAS, numeric, date ......

In your DATA step use

flag1993 = (start_date<='31DEC1993'd<=end_date);
flag1994 = (start_date<='31DEC1994'd<=end_date);
/* etc. */

Re: How to make an snapshot of a date for each respondent

mandan414 — Thu, 12 Nov 2020 16:25:03 GMT

That is great, but it will be very long since the dataset covers almost 50 years. I think I need to go with Do statement, but I'm not good at that. Thank you!

Re: How to make an snapshot of a date for each respondent

PaigeMiller — Thu, 12 Nov 2020 16:49:35 GMT

If you mean that you want to take the two lines I showed for 1993 and 1994, and you want similar for all years (for example) 1950 to 2000, then this is how you do it.

data want;
    set have;
    array flag flag1950-flag2000;
    year=1950;
    do i=1 to dim(yr);
        flag(i)=(start_date<=mdy(12,31,year)<=end_date);
        year=year+1;
    end;
    drop i year;
run;

Re: How to make an snapshot of a date for each respondent

ballardw — Thu, 12 Nov 2020 17:14:52 GMT

Another way creating one record per "flagged year";

data have;
  input student  start_date :date9. end_date :date9.;
  format start_date end_date date9.;
datalines;
1  04Sep1993 26jun1995
2  22Nov2002 20May2005
;

data want;
   set have;
   do d = start_date to end_date;
         d= intnx('year',d,0,'e');
         flagyear = year(d);
         if d le end_date then output;
         d=d+1;
   end;
   drop d;
run;