Reassigning variable causing unexpected behavior

[wh95]

I am trying to update a column as a date with yymmdd10 formatting, but am running into an issue and I am unclear on what is going wrong. Why, in the following code, do variables c and d end up in the correct type and format, but variable a does not?

data test1;
a = '2016-09-01';
a = input(a,yymmdd10.);
b = '2016-09-01';
c = input(b,yymmdd10.);
d = input('2016-09-01',yymmdd10.);
format a b c d yymmdd10.;
run;

[Astounding · Accepted solution]

Once a variable is numeric, it is always numeric.

Once a variable is character, it is always character. 

This line defines the variable A as character:

a = '2016-09-01';

While the next statement works and the INPUT function generates a number, SAS now has to store that number in a character variable.  So the result of the INPUT function gets converted back to character.  There should be a note on your log about numeric to character conversion taking place at that point.

[wh95]

That is unfortunate. Thank you for clearing that up. Is there a way to read in a variable as a date immediately? I have a column that has dates in the '2024-04-25' (yyyy-mm-dd) format, but they are being pulled in as character type. Why am I able to convert into a date type from a number, but not from a character?

[Patrick]

@wh95 You can't change the data type of a variable. In SAS it's either character or numeric. SAS dates are stored in a numeric variable as count of days since 1/1/1960.

If you create a new variable in SAS then the SAS compiler will define this variable based on the very first occurrence in the data step - so if you assign there a string to variable A then it becomes type character with the length of the string you assign - and after this it's fixed and you can't change it anymore.

You've got already multiple ways how to convert a character(string) into a SAS date value. Just don't try to then assign this SAS date value to a character variable.

[ballardw]

---

@wh95 wrote:

That is unfortunate. Thank you for clearing that up. Is there a way to read in a variable as a date immediately? I have a column that has dates in the '2024-04-25' (yyyy-mm-dd) format, but they are being pulled in as character type. Why am I able to convert into a date type from a number, but not from a character?

---

How are you "pulling in" the values and from what source?

If a field in an external database is character valued then depending on how you access the source SAS is likely to honor that.

If you are reading text files you should be able to specify the Informat to read a value:

data mydata;
   infile "<path>\sometextfilesource.txt" <other options>;
  informat datevariable yymmdd10.;
  input datevariable;
run;

SAS has multiple Informats to deal with most common and few less than common date structures.

[PaigeMiller]

---

@wh95 wrote:

Is there a way to read in a variable as a date immediately? I have a column that has dates in the '2024-04-25' (yyyy-mm-dd) format, but they are being pulled in as character type.

---

Pulled in from where? How are you pulling this in? Please give specifics about the source of this data and how you get it into SAS. Don't make us guess, don't make us assume. The more you tell us, the better the answers will be.

[Astounding]

It depends.  What is actually in the incoming data?  If it contains 10 characters (year, month, day, with dashes in between) then it is simple:

data want;
input datevar yymmdd10.;
format datevar yymmdd10.;
datalines;
2016-09-01
;

However, if the incoming data contains quotes, the solution is a little more complex:

data want;
input text $quote12.;
datevar = input(text, yymmdd10.);
format datevar yymmdd10.;
datalines;
'2016-09-01'
run;

It takes one statement to remove the quotes, then a second statement to convert the remaining text to a date value.

[wh95]

The data is being pulled in from a SQL table. The data in the date column does not have quotes. There is another column with dates formatted as yyyymm.

proc sql;
CREATE TABLE want AS
SELECT *
FROM x.table;
quit;

[PaigeMiller]

---

@wh95 wrote:
The data is being pulled in from a SQL table. The data in the date column does not have quotes. There is another column with dates formatted as yyyymm.

proc sql;
CREATE TABLE want AS
SELECT *
FROM x.table;
quit;

---

Above, you said that the date was '2024-04-25' which is a character string. Please run PROC CONTENTS on x.table and confirm that the date variable is either numeric or text/character. It really doesn't help to tell us what your human eyes see in this case, we need to know what PROC CONTENTS says.

[Patrick]

---

@wh95 wrote:
The data is being pulled in from a SQL table. The data in the date column does not have quotes. There is another column with dates formatted as yyyymm.

proc sql;
CREATE TABLE want AS
SELECT *
FROM x.table;
quit;

---

Please share the result from a Proc Contents of this SQL table so we can understand what we're dealing with.

[Tom]

---

@wh95 wrote:

That is unfortunate. Thank you for clearing that up. Is there a way to read in a variable as a date immediately? I have a column that has dates in the '2024-04-25' (yyyy-mm-dd) format, but they are being pulled in as character type. Why am I able to convert into a date type from a number, but not from a character?

---

There is no "date type" in SAS.  SAS only has two types of variables.  Floating point numbers and fixed length character strings.  Dates are stored as the number of days since 1960.  You can attach a format to display those day counts in many different styles depending on your needs. The YYMMDD10. format will display them in that YYYY-MM-DD style.  

You can convert a number into a date value in the same variable because they both are NUMERIC variables.  But you cannot store a date value in a character variable, only a representation of the date as a string, which is what you seem to be starting with.

If you want to re-use the name of the variable then just add a RENAME statement to change the names back around.

data want;
  set have;
  date_number = input(date,yymmdd10.);
  format date_number yymmdd10.;
  rename date_number=date date=date_character ;
run;

[Patrick]

Based on your code here sample syntax that will populate your variables with SAS Date values.

data test1;
  a_char = '2016-09-01';
  a = input(a_char,yymmdd10.);

  b = '01Sep2016'd;

  c = input('2016-09-01',yymmdd10.);
  format a b c yymmdd10.;
run;