Hello everyone, I am new to SAS and I am experiencing a lot of troubles these days, hope you guys can help me out please!! 😄
So I have two tables;
1. Employees' vacations info and
2. Contains the headers for each day of the month.
First I need to merge both tables so that I have the info of employees and right next to that I have the headers of every single day of the month, then I need to fill each column of the days with "0" or "1" depending if that exact date matches the range of the vacation.
Eg.
I tried with arrays but for some reason I keep getting errors, so I tried creating a variable that contains all days of the month and iterate it somehow, I just can´t find how to do that.
/*VARIABLE THAT CONTAINS ALL DAYS OF THE MONTH*/
%let fromDate = 01JUN2021; %let toDate = 30JUN2021; data want; length key 8;
do date = "&fromDate"d to "&toDate"d;
key + 1; workday = ( 2 <= weekday(date) <= 6);
output; end; format date date9. ; run;
THANKS A TON!!
Like this?
data WANT;
START_DTE='05JUN2021'd;
END_DTE ='07JUN2021'd;
array D[%sysevalf("&fromdate"d):%sysevalf("&todate"d)]
%macro loop; %local i; %do i=%sysevalf("&fromdate"d) %to %sysevalf("&todate"d);
V_%sysfunc(putn(&i.,date9.))
%end;; %mend; %loop
do DATE="&fromdate"d to "&todate"d;
D[DATE]=(START_DTE<=DATE<=END_DTE);
end;
run;
V_01JUN2021 | V_02JUN2021 | V_03JUN2021 | V_04JUN2021 | V_05JUN2021 | V_06JUN2021 | V_07JUN2021 | V_08JUN2021 | V_09JUN2021 | V_10JUN2021 | V_11JUN2021 | V_12JUN2021 | V_13JUN2021 | V_14JUN2021 | V_15JUN2021 | V_16JUN2021 | V_17JUN2021 | V_18JUN2021 |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 0 | 0 | 0 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Can you post the errors in the log you got when trying arrays and maybe post the code as well?
Like this?
data WANT;
START_DTE='05JUN2021'd;
END_DTE ='07JUN2021'd;
array D[%sysevalf("&fromdate"d):%sysevalf("&todate"d)]
%macro loop; %local i; %do i=%sysevalf("&fromdate"d) %to %sysevalf("&todate"d);
V_%sysfunc(putn(&i.,date9.))
%end;; %mend; %loop
do DATE="&fromdate"d to "&todate"d;
D[DATE]=(START_DTE<=DATE<=END_DTE);
end;
run;
V_01JUN2021 | V_02JUN2021 | V_03JUN2021 | V_04JUN2021 | V_05JUN2021 | V_06JUN2021 | V_07JUN2021 | V_08JUN2021 | V_09JUN2021 | V_10JUN2021 | V_11JUN2021 | V_12JUN2021 | V_13JUN2021 | V_14JUN2021 | V_15JUN2021 | V_16JUN2021 | V_17JUN2021 | V_18JUN2021 |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
0 | 0 | 0 | 0 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
OMG I am in shock!!!!!! This code works exactly the way I needed it to work!!!!
WOOOOOW... :') you did in 1 day what I was trying to do for 1 week!!! 😄
THANKS A LOOOOT FOR REAL!!!!! ❤️
Glad you find this useful. Thankfully it didn't take me a day lol.
Keep practicing and reading and you'll be able to easily do this too. 🙂
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.