topic Re: date substitution in SAS Programming

date substitution

sahoositaram555 — Wed, 22 Apr 2020 21:05:29 GMT

Hi,

I have a table below looks where i have a Numeric a column(like below date1) which is in YYMMDD10. format

I would like to update the date1(numeric) column with date2(originally in character,$CHAR10. format) where date1 is blank having the format of date1.

date1 Numer date2

2015-10-19 1101

1102 24/12/2005
1103 15/10/2003
1104 NULL
1105 12/10/2004

if date2 ~="" and date1=. then date1=input(date2,YYMMDD10.);

any ideas why the above code doesn't work?

Re: date substitution

PaigeMiller — Wed, 22 Apr 2020 21:25:37 GMT

What do you mean "doesn't work"?? What happens? Show us the log, please (the code in the log, plus the error messages and warning messages, do not chop anything out of the log)

Re: date substitution

ballardw — Wed, 22 Apr 2020 22:00:54 GMT

Actual data helps.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the <> icon or attached as text to show exactly what you have and that we can test code against.

And the complete data step code. You may have other issues. Post the LOG when running the code.

Your DATE2 values do not look like they are in YEAR/MONTH/Day order. They look like DAY MONTH YEAR. The informat YYMMDD expects year month day.

And NULL is right out.

Re: date substitution

Tom — Wed, 22 Apr 2020 23:37:00 GMT

Sounds like the variable DATE1 has date values, but many are missing.

Sounds like the variable DATE2 is a character string. And the values look like they are in D-M-Y order.

To convert DATE2 to a date value use the INPUT() function with the DDMMYY informat.

To change the value of DATE1 when DATE1 is missing you can use IF/THEN logic or the COALESCE() function.

Watch out for the values of DATE2 that have the string "NULL".

data want;
  set have;
  if missing(date1) and upcase(date2) not in (' ' 'NULL') then 
    date1 = input(date2,ddmmyy10.)
  ;
run;