Passing a date through a macro

[ssitharath0420]

Hello,

I have a created a date for these 2 date variables:

data _null_;
format tempd date9.;
tempd = intnx('qtr', today(), - %eval(2 + &adj.), 'begin');
pdstart_1=put(tempd,date9.);
call symput('pdstart_1',pdstart_1);

run;

data _null_;
tempd = intnx('qtr', today(), - %eval(1 + &adj.), 'end');
pdend_1=put(tempd,date9.);
call symput('pdend_1',pdend_1);
run;

I want to create a table where I can rename a field Total_members to that date every time I run my program.

proc sql;
create table TBL_1_Export_Period1 as
select distinct Status,Total_members as &pdstart_1to&pdend_1
from TBL_1_Export;
quit;

So basically it will look like this:

Status	10/1/19 - 03/31/2020
X1	470
X2	12.44
X3	23
X4	0.61
X5	493
Total	13.05

[PaigeMiller]

---

@ssitharath0420 wrote:

Hello,

I have a created a date for these 2 date variables:

data _null_;
format tempd date9.;
tempd = intnx('qtr', today(), - %eval(2 + &adj.), 'begin');
pdstart_1=put(tempd,date9.);
call symput('pdstart_1',pdstart_1);

run;

data _null_;
tempd = intnx('qtr', today(), - %eval(1 + &adj.), 'end');
pdend_1=put(tempd,date9.);
call symput('pdend_1',pdend_1);
run;

I want to create a table where I can rename a field Total_members to that date every time I run my program.

proc sql;
create table TBL_1_Export_Period1 as
select distinct Status,Total_members as &pdstart_1to&pdend_1
from TBL_1_Export;
quit;

So basically it will look like this:

Status	10/1/19 - 03/31/2020
X1	470
X2	12.44
X3	23
X4	0.61
X5	493
Total	13.05

---

You can't have it look like that, with dashes and slashes in a variable name like 10/1/19 - 03/31/2020

You could place the text 10/1/19 - 03/31/2020 into a variable label and then there's no problem.

But if you want 10/1/19 - 03/31/2020 why are you using date9. format which doesn't have slashes? Perhaps you need mmddyys10. format which does have slashes. But then you'd get 10/1/2019 - 03/31/2020 and not 10/1/19 - 03/31/2020, is that acceptable?

Additionally, in my opinion it is rarely a good idea to put dates (or other data) into variable names. Depending on what you are doing, there are much better ways to handle dates, but we don't really know the final goal of this work, we only know the intermediate goal of labeling columns as 10/1/19 - 03/31/2020

[ssitharath0420]

Hi Page, 

It doesn't matter whether the date is format date9. or MMDDYY10.  I was just showing an example.  I want the column to be a date field because I have a code that I am running for a whole year but the data is broken out into 6 months interval therefore I want the column to be date specific.  I can manually name it once I create a table for a specific period but there are three other periods I need to include in my report and I just want it to not be manually updated for each time it runs for a specific period. I hoped that make sense.

[mkeintz]

Do you really want a variable with a name like (I've modified it to have mm/dd/yyyy format for both sides of the "-"):

     10/01/2019-03/31/2020
?

That would require using name literals.  First you would have to set

  options validvarname=any;

if it isn't already set.  And you would then have to refer to the variable forever afterwards as "10/01/2019-03/31/2020"n, as in:

%let pdstart_1=%sysfunc(intnx(qtr,%sysevalf("&sysdate9"d),-%eval(2+&adj),begin),mmddyys10.);
%let pdend_1=%sysfunc(intnx(qtr,%sysevalf("&sysdate9"d),-%eval(1+&adj),end),mmddyys10.);

proc sql;
  create table TBL_1_Export_Period1 as
  select distinct Status
      ,Total_members as "&pdstart_1.-&pdend_1"n
      from TBL_1_Export;
quit

BTW, note I used a %LET statement including the %sysfunc and %sysevalf macro functions to replace the data steps you used to assign values to macrovars  PDSTART_1 and PDEND_1.  

If you were going to do this, I'd change the date components of each name from mm/dd/yyyy to yyyy/mm/dd (change the formats from mmddyys10. to yymmdds10).  Then if you have several such names, an alphabetic sort of them would correspond to chronological order.

You would NOT have to do this stuff if

1. You used a format like yymmddn8  (i.e. no slashes).
2. Renamed total_members to something like TM_yyyymmdd_yyyymmdd.

i.e. change

      ,Total_members as "&pdstart_1.-&pdend_1"n

to

      ,Total_members as TM_&pdstart_1._&pdend_1

[ssitharath0420]

Thank you!

[ssitharath0420]

Hi, 

I am getting an error of ERROR: The value 10/01/2019-03/31/2020 is not a valid SAS name.

with this code:

proc sql;
create table TBL_1_Export_Period1 as
select distinct Status
,Total_members as "&pdstart_1.-&pdend_1"n
from TBL_1_Export;
quit;

[mkeintz]

You didn't send the log, so I don't know if you carefully read my complete response.  In particular, did you specify OPTIONS VALIDVARNAME=ANY;  in your program?

[ssitharath0420]

Oops! I just did and it worked! Thank you!