format change by using proc sql based on variable length

[dht115]

Hello, 

I need to write following code in proc sql. 

Are we able to use when - then conditional statement in proc sql to create similar output? 

How can we use strip function in proc sql? 

	data test1;
	  set test2;
	  if length(strip(_datetime)) GE 10 then do;
	  _date1=input(strip(_datetime),??yymmdd10.);
      _years=year(input(strip(_datetime),??yymmdd10.));
      _months=month(input(strip(_datetime),??yymmdd10.));
      _days=day(input(strip(_datetime),??yymmdd10.));
	  end;
     
      if length(strip(_datetime)) LT 9 then do;
      _years=input(scan(_datetime,1),8.);
      _months=input(scan(_datetime,2),8.);
      _days=input(scan(_datetime,3),8.);
	  end;
	  format _date1 yymmdd10.;
	  run;

[PaigeMiller]

What are some typical values of _datetime? Is it character or numeric? What is the format assigned to _datetime?

Normally it is a poor choice to store calendar dates as a year variable, a month variable and a day variable. Why are you doing this?

[dht115]

_datetime variable is a character variable. 

• _datetime $19
• YYYY-MM-DDTHH:MM:SS

Example:

2017-07-20T09:45:27

[PaigeMiller]

So why do you need this?

 if length(strip(_datetime)) LT 9 then do;

And please answer my other question: "Normally it is a poor choice to store calendar dates as a year variable, a month variable and a day variable. Why are you doing this?"

[dht115]

It is one of the requirement. Do not know the reason behind it 😞 

[PaigeMiller]

---

@dht115 wrote:

It is one of the requirement. Do not know the reason behind it 😞 

---

Then I suggest you ask questions, because as I said, it is normally a poor idea to store calendar dates as a year variable, a month variable and a day variable, and my advice to you is to NOT do this at all. Leave dates as dates.

What about this question? I don't see an answer

So why do you need this?

 if length(strip(_datetime)) LT 9 then do;

[Tom]

---

@dht115 wrote:

_datetime variable is a character variable. 

• _datetime $19
• YYYY-MM-DDTHH:MM:SS

Example:

2017-07-20T09:45:27

---

So use an INFORMAT that understands that type of a string.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/leforinforref/p0jd9jaqmxd2ezn1kncmpgfqcpiz.ht...

actual_datetime = input(_datetime,E8601DT19.);
actual_date = datepart(actual_datetime);
format actual_date yymmdd10. actual_datetime datetime19.;

[Tom]

In SQL you cannot have the DO/END block.  So you need to repeat the test for each of the three target variables.

I will show you how to do the _YEARS and you can replicate for the other two.

create table test2 as
select *
     , case when (length(strip(_datetime)) GE 10 ) input(strip(_datetime),?yymmdd10.) 
            else . 
       end as _date1 format=yymmdd10.
     , case when (length(strip(_datetime)) GE 10 ) then _years=year(calculated _date1)
            else input(scan(_datetime,1),8.)
       end as _years
 /* repeat for _months and _days */
from test1
;

[dht115]

Hello, 

I am receiving following error when I run these code: 

• ERROR: Function YEAR requires a numeric expression as argument 1.

data test1;
input _datetime $19.;
datalines;
2021-11-29T05:30:00
17MAR2012
2022-08
;
run;

proc sql;
create table test_3 as
select *, case 
	when (length(strip(_datetime)) GE 10 ) then input(strip(_datetime),?yymmdd10.)
	when (length(strip(_datetime)) EQ 9 ) then input(strip(_datetime),date9.)
       end as _date1 format=yymmdd10.
	, case
	when (length(strip(_datetime)) GE 10 ) then _years=year(_date1)
	end as _years
 from test1
;

[Tom]

If you want to reference a variable you have created as part of the current SELECT statement then you need to add the CALCULATED keyword.

year(calculated _date1)

[dht115]

Hello, 

I am getting an error: 

• ERROR: The following columns were not found in the contributing tables: _years.

data test1;
input _datetime $19.;
datalines;
2021-11-29T05:30:00
17MAR2012
2022-08
;
run;

proc sql;
create table test_3 as
select *, case 
	when (length(strip(_datetime)) GE 10 ) then input(strip(_datetime),?yymmdd10.)
	when (length(strip(_datetime)) EQ 9 ) then input(strip(_datetime),date9.)
       end as _date1 format=yymmdd10.
	, case
	when (length(strip(_datetime)) GE 10 ) then _years=year(calculated _date1)
	end as _years
	from test1
;

[PaigeMiller · Accepted solution]

when (length(strip(_datetime)) GE 10 ) then _years=year(calculated _date1) end as _years

_years is not in data set TEST1, so you can't use it in a THEN clause

By the way, I think you want

when (length(strip(_datetime)) GE 10 ) then year(calculated _date1) end as _years

[ballardw]

Waste of clock cycles to do this :

 _years=year(input(strip(_datetime),??yymmdd10.));

You have already created a date value so it would make infinitely more sense to use that then to re-convert the character value to date and then extract year (or month or day)

_years = year(_date1);

Testing for length like that only makes sense in the context changing length values in the character value. If you don't want the code to execute when the _datetime is blank(i.e. missing) then test for not missing makes more sense and lets someone reading the code later know why the test is there at all.