topic Reassigning variable causing unexpected behavior in SAS Programming

Reassigning variable causing unexpected behavior

wh95 — Fri, 26 Apr 2024 01:56:37 GMT

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;

Re: Reassigning variable causing unexpected behavior

Astounding — Fri, 26 Apr 2024 03:02:55 GMT

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.

Re: Reassigning variable causing unexpected behavior

Patrick — Fri, 26 Apr 2024 03:10:25 GMT

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;

Re: Reassigning variable causing unexpected behavior

wh95 — Fri, 26 Apr 2024 04:17:50 GMT

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?

Re: Reassigning variable causing unexpected behavior

Patrick — Fri, 26 Apr 2024 05:13:41 GMT

@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.

Re: Reassigning variable causing unexpected behavior

ballardw — Fri, 26 Apr 2024 09:06:02 GMT

@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.

Re: Reassigning variable causing unexpected behavior

PaigeMiller — Fri, 26 Apr 2024 09:49:45 GMT

@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.

Re: Reassigning variable causing unexpected behavior

Astounding — Fri, 26 Apr 2024 09:58:35 GMT

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.

Re: Reassigning variable causing unexpected behavior

wh95 — Fri, 26 Apr 2024 14:37:21 GMT

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;

Re: Reassigning variable causing unexpected behavior

PaigeMiller — Fri, 26 Apr 2024 14:51:21 GMT

@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.

Re: Reassigning variable causing unexpected behavior

Patrick — Sat, 27 Apr 2024 00:22:57 GMT

@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.

Re: Reassigning variable causing unexpected behavior

Tom — Sat, 27 Apr 2024 15:37:54 GMT

@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;