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;
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 ________________.
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 ________________.
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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.