SAS, picking the wrong date or weekday

user_15609528 0
Viewed 162

Hi Everybody

I am new to the SAS world and from a few days ago got this challenge to find out why this job failed. I quickly spotted that the error might lie in the way the date or days of the week were defined. If you look at the scare picture, you can clearly see that the dates do not fit. The program tries to find Wednesday, where the date it was supposed to find was a Tuesday.

It's not me who developed the code unfortunately ..

Hope some of you can help me.

Thank you for your time

Screenshot to clarify

*%let aar = %eval( %sysfunc(year(%sysfunc(today())))-1); *Til brug ved årsskifte;

%let maaned_start= %eval( %sysfunc(month(%sysfunc(today())))-1);
%let maaned_slut=  %eval( %sysfunc(month(%sysfunc(today())))-1);
%let sidste_maaned=  %eval( %sysfunc(month(%sysfunc(today())))-2);

%put &=aar;
%put &=maaned_start;
%put &=maaned_slut;
%put &=sidste_maaned;

libname Trafik "\\sascortst01\SAS_dw\YouSee\Trafikdata";

data Trafik;
    input OLT_name $20. macaddress $14. Date1 $10. down_mbit up_mbit;
    date=input(date1,ddmmyy10.);
    format date ddmmyy10.;
    datalines;
    IdasOltprøveaaaaaaa aaaa-aaaa-aaaa 01/01/01 9999999 9999999
;
run;

data trafik (drop = date1);
retain OLT_name macaddress Date down_mbit up_mbit;
set trafik;
run;

%macro trafik (trafik);
%let aar = %eval( %sysfunc(year(%sysfunc(today()))));
%let maaned_start= %eval( %sysfunc(month(%sysfunc(today())))-1);
%let maaned_slut=  %eval( %sysfunc(month(%sysfunc(today())))-1);

%if &maaned_start. <= 9 %then %do;
  %do m=&maaned_start. %to &maaned_slut.;
  %do d=1 %to 9;
  proc sql;
  create table Trafik_mac_&aar.0&m.0&d._&trafik.
  as select distinct a.cmts_name as OLT_name, a.macaddress, b.lid, datepart(a.start_rec_creation_time) format ddmmyy10. as Date, round(sum(a.down_qos_service_octets_passed)*8/1000000,0.001) as down_mbit, 
  round(sum(a.up_qos_service_octets_passed)*8/1000000,0.001) as up_mbit 
  from trafik.Trafik_mac_&aar.0&m.0&d._&trafik. a left join trafik.Macaddress_info_&aar.0&m.0&d._&trafik. b on a.macaddress=b.macaddress
  group by a.cmts_name, a.macaddress, datepart(a.start_rec_creation_time) order by a.cmts_name, a.macaddress, datepart(a.start_rec_creation_time) desc;
  quit;
  proc sort data=Trafik_mac_&aar.0&m.0&d._&trafik. nodupkey; by OLT_name macaddress; run;
  data trafik;
  set trafik Trafik_mac_&aar.0&m.0&d._&trafik.;
  run;
%end;

%do d=10 %to 31;
proc sql;
create table Trafik_mac_&aar.0&m.&d._&trafik.
as select distinct a.cmts_name as OLT_name, a.macaddress, b.lid, datepart(a.start_rec_creation_time) format ddmmyy10. as Date, round(sum(a.down_qos_service_octets_passed)*8/1000000,0.001) as down_mbit, 
round(sum(a.up_qos_service_octets_passed)*8/1000000,0.001) as up_mbit 
from trafik.Trafik_mac_&aar.0&m.&d._&trafik. a left join trafik.Macaddress_info_&aar.0&m.&d._&trafik. b on a.macaddress=b.macaddress
group by a.cmts_name, a.macaddress, datepart(a.start_rec_creation_time) order by a.cmts_name, a.macaddress, datepart(a.start_rec_creation_time) desc;
quit;
proc sort data=Trafik_mac_&aar.0&m.&d._&trafik. nodupkey; by OLT_name macaddress; run;
data trafik;
set trafik Trafik_mac_&aar.0&m.&d._&trafik.;
run;
%end;
%end;
%end;

%if &maaned_slut. >= 10 %then %do;
%do m=&maaned_start. %to &maaned_slut.;
%do d=1 %to 9;
proc sql;
create table Trafik_mac_&aar.&m.0&d._&trafik.
as select distinct a.cmts_name as OLT_name, a.macaddress, b.lid, datepart(a.start_rec_creation_time) format ddmmyy10. as Date, round(sum(a.down_qos_service_octets_passed)*8/1000000,0.001) as down_mbit, 
round(sum(a.up_qos_service_octets_passed)*8/1000000,0.001) as up_mbit 
from trafik.Trafik_mac_&aar.&m.0&d._&trafik. a left join trafik.Macaddress_info_&aar.&m.0&d._&trafik. b on a.macaddress=b.macaddress
group by a.cmts_name, a.macaddress, datepart(a.start_rec_creation_time) order by a.cmts_name, a.macaddress, datepart(a.start_rec_creation_time) desc;
quit;
proc sort data=Trafik_mac_&aar.&m.0&d._&trafik. nodupkey; by OLT_name macaddress; run;
data trafik;
set trafik Trafik_mac_&aar.&m.0&d._&trafik.;
run;
%end;

%do d=10 %to 31;
proc sql;
create table Trafik_mac_&aar.&m.&d._&trafik.
as select distinct a.cmts_name as OLT_name, a.macaddress, b.lid, datepart(a.start_rec_creation_time) format ddmmyy10. as Date, round(sum(a.down_qos_service_octets_passed)*8/1000000,0.001) as down_mbit, 
round(sum(a.up_qos_service_octets_passed)*8/1000000,0.001) as up_mbit 
from trafik.Trafik_mac_&aar.&m.&d._&trafik. a left join trafik.Macaddress_info_&aar.&m.&d._&trafik. b on a.macaddress=b.macaddress
group by a.cmts_name, a.macaddress, datepart(a.start_rec_creation_time) order by a.cmts_name, a.macaddress, datepart(a.start_rec_creation_time) desc;
quit;
proc sort data=Trafik_mac_&aar.&m.&d._&trafik. nodupkey; by OLT_name macaddress; run;
data trafik;
set trafik Trafik_mac_&aar.&m.&d._&trafik.;
run;
%end;
%end;
%end;

%mend trafik;
%trafik(MON);
%trafik(TUE);
%trafik(WED);
%trafik(THU);
%trafik(FRI);
%trafik(SAT);
%trafik(SUN);

data trafik (drop = month);
set trafik;
if OLT_name = "IdasOltpr%" then delete;
if macaddress = "aaaa-aaaa-aaaa" then delete;
Mont=month(date);
Year=year(date);
run;
sas
1 Answers

It's hard to tell exactly what's going on in your program, but first create the date that you need as a SAS date. This can be done through the intnx() function to shift your required date. For example, if you needed to get data from the last two months using the exact same day as today:

%let today = %sysfunc(today() );

%let start = %sysfunc(intnx(month, &today., -2) );
%let end   = %sysfunc(intnx(month, &today., -1) );

From there, pull each individual element out to build the file name, such as year, month, and day. Grab the weekday name of the SAS date with the downame3. format. For example:

%let date    = %sysfunc(today() );
%let weekday = %lowcase(%sysfunc(putn(&date., downame3.) ) );

%let year    = %sysfunc(year(&date.) );
%let month   = %sysfunc(month(&date.), z2.);
%let day     = %sysfunc(day(&date.), z2.);

%put Filename: trafik_mac_&year.&month.&day._&weekday.;

Output:

Filename: trafik_mac_20210921_tue
user_53427000
Related