Dummy variables for dates having a start dat and end day

[Van2]

Dear sas community,

im fairly new at sas and have a problem i hope you Can help me solve. My dataset looks like this

id.   Moveindate moveoutdate

a      Jan. 1.2021.          Jan.4.2021

b.      Jan.2.2021.         Jan.3.2021

i Want to create dummy columns for each day of the Month that take the value 1 if the date is within the range of moveindate and moveoutdate. So for example it would look like this

id.   Moveindate moveoutdate jan1 jan2 jan3 jan4

a      Jan. 1.2021.          Jan.4.2021 1.    1.        1.      1

b.      Jan.2.2021.         Jan.3.2021 0.   1.        1.       0

i Can do this the brute Way by using datastep like Below, but its tedious and error prone since i eventually plan on doing it for every month of the year. I hope you Guys Can help. Thanks in advance

data want;

set have;

jan1=0;

if moveindate<=`01jan2021` and moveoutdate=>´01jan2021` then jan01=1;

run;

[PaigeMiller · Accepted solution]

Creating variables named JAN1, JAN2, JAN3, JAN4 is a particularly poor way of arranging the data in SAS, and makes future programming with this data set more difficult and time consuming. Dates are data, they belong as values of a variable, not in a variable name.

A much better approach is to leave the data in the long arrangement. This assumes moveindate and moveoutdate are valid numeric SAS date values.

data re_arrange;
    set have;
    do date=movindate to moveoutdate by 1;
        output;
     end;
     drop moveindate moveoutdate;
run;

Further calculations with this data set are much easier because almost all SAS PROCs work well on data in this long arrangement.

Please answer this question: the next steps after this re-arrangement of the data will be to perform analyses or reports or plots like _______________ and ________________.

[Van2]

thank you very much, I just implemented your method, and I must say that this is a much more elegant and straightforward solution to my problem. Kudos.

[ballardw]

What do you expect to do with that resulting data? As in EXACTLY how are you using it.

The set that you show with "wide" values containing data in the name of the variable is extremely hard to work with in any reasonable way. It is almost certainly going to be easier to use a long form with one observation per date (actual date, including a year). Make a report wide for readability at the end perhaps but in between the long form for manipulation works much better.

Do you current "dates" actually have text that looks like "Jan.4.2021"? You are inconsistent by showing some values with periods at the end of the values. If you have text the first step regardless is to get an actual SAS date value in a variable.

Or do you even have a SAS data set?

Here is an approach to create SAS date values and use them to make a long set and a brief example of a wide report.

data have;
  input id $  Movein :$10. moveout :$10.;
datalines;
a      Jan.1.2021          Jan.4.2021
b.      Jan.2.2021         Jan.3.2021
;

data want;
  set have;
  moveindate= input(movein,anydtdte30.);
  moveoutdate= input(moveout,anydtdte30.);
  format moveindate moveoutdate date9.;
  do dt = moveindate to moveoutdate;
     dummy=1;
     output;
  end;
  format dt date9.;
  drop movein moveout;
run;

proc report data=want;
   columns id dt,dummy;
   define id /group;
   define dt /across;
   define dummy/ analysis sum;
run;

Once you have a SAS date value then you can display it just about any way that you want. This shows the dates as MM-DD without the year:

proc report data=want;
   columns id dt,dummy;
   define id /group;
   define dt /across format=mmddyyd5.;
   define dummy/ analysis sum;
run;

A custom format could be made to make a MON-DD , Jan-01 type appearance if needed.

[Van2]

Thanks to both of you guys, much appreciated. I am ultimately interested in counting the number of unique ids at any given day of the year, in order to get a sense of how many guests there are in a year, and which periods (if any) have a particularly high or low number of people. I apologize for the poor writing, the op was quickly written on a phone (and I’m not a native English speaker if there was poor grammar). I will implement your suggestions, and again many thanks.

[PaigeMiller]

Counting unique IDs on any given day is a piece of cake with the long arrangement of the data that I showed.

proc freq data=re_arrange;
    tables date;
run;

[ballardw]

If you don't have a person more than once on a given day then using the previous example want set one way:

proc freq data=want;
  tables dt;
run;