reading date

[Aexor]

Hi I  want to append data from his_new to hist_old table but the issue is I am not getting proper format for below dates coloumn max_date and min date. Please note that I am getting min_date and max_date avlues  from macro variable

data hist_entry;
format entity_name $18. ;
format date yymmdd10. ;
format checksum_column_nm $12. ;
format checksum_column_val best32. ;
format date_column_nm $16. ;
format record_count best32. ;
format active_flag $1. ;
format distinct_date_count best32. ;
informat max_date yymmdd10. ;
informat min_date yymmdd10. ;
format max_date yymmdd10. ;
format min_date yymmdd10. ;
entity_name="invoice";
date=today();
checksum_column_nm="sell_qty";
checksum_column_val= 4102745354;
date_column_nm= "sell_dt";
record_count=4586000;
active_flag="Y";
distinct_date_count = 50;
max_date = 2022-03-06;
min_date =2022-01-16;
output;
run;
proc append base=hist_old data=hist_new ;
run;

the issue is for max_date value is coming as 1965-07-06 and min_date is coming as 1965-06-28 I am not sure why this is happening . Could you please help.

Also max_date = 2022-03-06;
min_date =2022-01-16; these values are getting resolved from macro variables.

[PaigeMiller · Accepted solution]

max_date = 2022-03-06;

Max_date is interpreted in SAS as the integer 2022 minus the integer 03 minus the integer 06. I expect this is not what you wanted.

Try this:

max_date='06MAR2022'd;

This is how the translation is done from what humans understand (March 6, 2022) to what SAS understands, number of days since 01JAN1960. You must use the format shown in the code for max_date, and not any other format (except that the letters can be lower case).

Alternatively, you could use an informat in the PUT function (but not in an INFORMAT statement).

max_date=input('2022-03-06',yymmdd10.);

[Tom]

Use date literals to enter specific date values.  Date literals must use a string inside the quotes that the DATE informat can understand as a date.

Your code seems to be trying to use FORMAT statement as if it was intended to define the variables, instead of what it really does which is just to attach any special instructions you want to use to display the values.

If you already have a dataset that defines the variables just use that to define the structure, then you are sure the variables in your new dataset will match those that you are trying to append them to.  By using an obviously false condition in the IF/THEN the SET statement will never execute, but the dataset reference will cause its variables to be defined in the new dataset.  If you are only generating one observation there is no need for the OUTPUT statement.

data hist_entry;
  if 0 then set hist_old;

  entity_name="invoice";
  date=today();
  checksum_column_nm="sell_qty";
  checksum_column_val= 4102745354;
  date_column_nm= "sell_dt";
  record_count=4586000;
  active_flag="Y";
  distinct_date_count = 50;
  max_date = '06MAR2022'd;
  min_date = '16JAN2022'd;

run;

proc append base=hist_old data=hist_new ;
run;