sas variable & date issues

[me55]

this is one of the things i find most confusing and frustrating about sas...you can use certain formats in certain formulas/situations but not others.  it is very simple what i am trying to do and i assume the answer is simple but i just cannot get it to work right.  i need to great a global variable to use in date formatted fields (MM/DD/YY). 

this just is not working and i am completely confused now.  i need eom to come out with the last day of last month in the format MM/DD/YY and put it into a field (date_1) in table (table_1).  this is close but just something is wrong...

data _null_;
eom1=put(intnx('month',today(),-1,'E'),MMDDYY6.);
eom2=put(intnx('month',today(),-1,'E'),MMDDYY8.);
call symput('efiledt1',eom1);
call symputx('efiledt2',put(eom2,mmddyyn8.));
run;


data TABLE_1;
set TABLE_1;
DATE_1="&efiledt2"d;
run;

it works for eom1 putting that into a character field but i cannot get eom2 to go into a date formatted field in the format MM/DD/YY. 

[JBailey]

Hi @me55,

Does this help?

data _null_;
   format date mmddyy10.;
   date = intnx('month', today(), 0, 'E');
   put date=;
run;

Here is the SAS Log.

89   data _null_;
90      format date mmddyy10.;
91      date = intnx('month', today(), 0, 'E');
92
93      put date=;
94   run;

date=07/31/2019
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds

Best wishes,
Jeff

[me55]

no, that does not work.  there are two issues, putting the date in and getting it out.  i know part, putting it in using symput function puts it in character format which will not go into a date formatted field...(filed_1 date format=MMDDYY8. informat=MMDDYY8.).  so using that i can get the right date in there but in the wrong format.  so i too need to get it out in that format and i am going to use that global variable like 10 times throughout the code and it needs to be a global variable too.  

proc sql;
create table TABLE_1(
FIELD_1 date format=MMDDYY8. informat=MMDDYY8.);
quit;

data _null_;
	format efiledt2 mmddyy8.;
	efiledt2 = intnx('month',today(),-1,'E');
run;

data TABLE_1;
  set TABLE_1;
	FIELD_1="&efiledt2"d;
run;

[Astounding]

When you store a date in a variable, the format doesn't matter. But when you refer to a specific day in a literal expression the format is important:

"&efiledt2"d

The expression in quotes must be in the DATE9 format:

"31JUL2019"d

[me55]

that is good to know but that doesnt really help me get what i need.  what i need is a global variable that i can put into a field in format mm/dd/yy.  even if i can put it in there in many ways, i need it out in mm/dd/yy format.  i had a few errors in the code, here is what i am trying to do...

proc sql;
create table TABLE_1 (
FIELD_1 date format=MMDDYY8. informat=MMDDYY8.);
quit;

data _null_;
	format efiledt2 mmddyy8.;
	efiledt2 = intnx('month',today(),-1,'E');
run;

data TABLE_1;
  set TABLE_1;
	FIELD_1=&efiledt2;
run;

[PaigeMiller]

---

@me55 wrote:

that is good to know but that doesnt really help me get what i need.  what i need is a global variable that i can put into a field in format mm/dd/yy.  even if i can put it in there in many ways, i need it out in mm/dd/yy format.  i had a few errors in the code, here is what i am trying to do...

proc sql;
create table TABLE_1 (
FIELD_1 date format=MMDDYY8. informat=MMDDYY8.);
quit;

data _null_;
	format efiledt2 mmddyy8.;
	efiledt2 = intnx('month',today(),-1,'E');
run;

data TABLE_1;
  set TABLE_1;
	FIELD_1=&efiledt2;
run;

---

Well, if you need it in a particular format, you format the variable FIELD_1 with a FORMAT statement, rather than format the macro variable.

As stated above, you do not need formatted macro variables (unless you are going to use them in a title or label, not the case here).

[JBailey]

Hi,

An additional problem is you are reading from an empty table and then writing back to it.

Best wishes,

Jeff

[PaigeMiller]

The general rule that I follow (and advise you to follow) is that you do not format macro variables. It is unnecessary to format macro variables for use in SAS code ... with the exception of titles and labels, where you probably do want it formatted.

data _null_;
eom1=intnx('month',today(),-1,'E');
eom2=intnx('month',today(),-1,'E');
call symput('efiledt1',eom1);
call symputx('efiledt2',eom2);
run;

data TABLE_1;
set TABLE_1;
DATE_1=&efiledt2;
/* OPTIONAL */ format date_1 mmddyy8.;
run;

[me55]

oh, i see what you are saying paigemiller.  i thought the intnx needed a format on it.  so i tried...

proc sql;
create table TABLE_1 (
FIELD_1 date format=MMDDYY8. informat=MMDDYY8.);
quit;

data _null_;
eom1=intnx('month',today(),-1,'E');
eom2=intnx('month',today(),-1,'E');
call symput('efiledt1',eom1);
call symputx('efiledt2',eom2);
run;

data TABLE_1;
set TABLE_1;
FIELD_1=&efiledt2;
run;

...and that puts nothing into TABLE_1, FIELD_1. 

[JBailey]

Hi @me55 

The example that I sent is a good one for seeing how this works. Plus, it is simple.

I do think you have a problem with trying to run this on an empty table and then write back to the empty table. 

Best wishes,

Jeff

[PaigeMiller · Accepted solution]

Well you have other SAS errors in your code, such as create a data set TABLE_1 from PROC SQL which has 0 rows.

Then when you do 

data TABLE_1;
set TABLE_1;
FIELD_1=&efiledt2;
run;

you still have zero rows. However, if you do

data TABLE_1;
FIELD_1=&efiledt2;
run;

Now you have one row, and the variable FIELD_1 contains the desired date.

Also, INTNX works on unformatted date values only. These must be valid SAS date values, which are integers. If you have a formatted date somehow, then you have to properly unformat it, which is extra work. And so the advice is to not format your dates for use in INTNX (although certainly formatted dates have other uses).

[me55]

yeah, sometimes when i get frustrated i put blinders on.  thanks all, that worked great!