BookmarkSubscribeRSS Feed
SAS_INFO
Quartz | Level 8

hi All,

 

 

data _null_;

today = today();

day_of_week = weekday(today);

if day_of_week = 4

then do;

start_date = today()-5;

end_date = today()-2;

end;

else do;

start_date = today()-2;

end_date = today()-2;

end;

days = end_date - start_date;

call symput('start',put(start_date,yymmddn8.));

call symput('end',put(end_date,yymmddn8.));

run;

 

data _null_;

curr_year = year(today());

cu_month = month(today());

La_month = month(today())-1;

length string1 $6 string2 $6;

string1= put(curr_year,z4.) !! put(cu_month,z2.);

string2= put(curr_year,z4.) !! put(La_month,z2.);

call symput('current_month',string1);

call symput('Last_Month',string2);

run;

  

data temping;

keep mer_id trn_ac_nbr proc_dt;

set libr.ab002_&current_month. libr.ab002_&Last_Month.;

where proc_dt between &start. and &end.;

run;

 

 

the above code ran with 0 onservations , though the datasets had data.

 

I guess the error is in the datepart/format /informat i use in &start.and *end. macro.

 

also Proc_dt is in datatime20.

 

 

 

5 REPLIES 5
Jagadishkatam
Amethyst | Level 16
Could you please share the log
Thanks,
Jag
andreas_lds
Jade | Level 19

Is proc_dt a numeric variable with datetime format attached, or is it alphanumeric? if the proc_dt is numeric than changing

call symput('start',put(start_date,yymmddn8.));

call symput('end',put(end_date,yymmddn8.));

to

call symput('start', start_date);

call symput('end', end_date);

 

will solve the issue.

cpagrawal
Fluorite | Level 6

The calculation of last month can be modified, I mean what will happen if cu_month = 1, then the la_month and year will change. 
also If proc_dt is compared with macro variables it should be date constant. 
Like 

call symput('start',put(start_date,date9.));
call symput('end',put(end_date,date9.));

and where condition should be : 

where proc_dt between "&start."d and "&end."d;

 

the calculation of start and end can be modified using sas date functions.

Kurt_Bremser
Super User

When you do this:

call symput('start',put(start_date,yymmddn8.));
call symput('end',put(end_date,yymmddn8.));

you create macro values of the form

20170801

Then you issue this WHERE condition:

where proc_dt between &start. and &end.;

Which will end up like this after the macrovars are resolved:

where proc_dt between 20170801 and 20170801;

If proc_dt is a SAS date value (days from 1960-01-01), this cannot work.

Therefore follow my Maxim 28 and store dates as the raw numerical values into macro variables:

call symput('start',put(start_date,best.));
call symput('end',put(end_date,best.));

Also note that these calculations won't work in January:

data _null_;
curr_year = year(today());
cu_month = month(today());
La_month = month(today())-1;
length string1 $6 string2 $6;
string1= put(curr_year,z4.) !! put(cu_month,z2.);
string2= put(curr_year,z4.) !! put(La_month,z2.);
call symput('current_month',string1);
call symput('Last_Month',string2);
run;

When determining previous or following periods, always use the intnx() function and the derive year and month values off the result.

Astounding
PROC Star

There are two issues here.  First, as Kurt mentioned, except on Thursdays, START_DATE = END_DATE.  It looks like a little revision to the formulas might be necessary.

 

Second, dates and datetimes are on completely different scales.  You won't find a datetime value falling BETWEEN two date values.  If PROC_DT is a true SAS datetime value, you would need:

 

where datepart(proc_dt) between &start. and &end.;

 

If PROC_DT is actually a character string (and a PROC CONTENTS will tell you this), the formula is more complex.  It might look like this, but I would need to see the form of the values in PROC_DT and possibly fiddle with the formula to be sure:

 

where datepart( input(proc_dt,anydtdte.) ) between &start. and &end.;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1144 views
  • 0 likes
  • 6 in conversation