topic Re: format change by using proc sql based on variable length in SAS Programming

format change by using proc sql based on variable length

dht115 — Thu, 25 Aug 2022 13:43:07 GMT

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;

Re: format change by using proc sql based on variable length

PaigeMiller — Thu, 25 Aug 2022 13:52:58 GMT

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?

Re: format change by using proc sql based on variable length

Tom — Thu, 25 Aug 2022 14:08:00 GMT

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
;

Re: format change by using proc sql based on variable length

dht115 — Thu, 25 Aug 2022 14:14:50 GMT

_datetime variable is a character variable.

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

Example:

2017-07-20T09:45:27

Re: format change by using proc sql based on variable length

PaigeMiller — Thu, 25 Aug 2022 14:20:31 GMT

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?"

Re: format change by using proc sql based on variable length

dht115 — Thu, 25 Aug 2022 14:21:44 GMT

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

Re: format change by using proc sql based on variable length

PaigeMiller — Thu, 25 Aug 2022 14:32:31 GMT

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

Re: format change by using proc sql based on variable length

ballardw — Thu, 25 Aug 2022 15:15:21 GMT

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.

Re: format change by using proc sql based on variable length

dht115 — Thu, 25 Aug 2022 15:28:53 GMT

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
;

Re: format change by using proc sql based on variable length

Tom — Thu, 25 Aug 2022 15:41:36 GMT

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)

Re: format change by using proc sql based on variable length

Tom — Thu, 25 Aug 2022 15:47:11 GMT

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

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

Re: format change by using proc sql based on variable length

dht115 — Thu, 25 Aug 2022 16:00:50 GMT

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
;

Re: format change by using proc sql based on variable length

PaigeMiller — Thu, 25 Aug 2022 16:08:16 GMT

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