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;
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
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?
_datetime variable is a character variable.
Example:
2017-07-20T09:45:27
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?"
It is one of the requirement. Do not know the reason behind it 😞
@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;
@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.
actual_datetime = input(_datetime,E8601DT19.);
actual_date = datepart(actual_datetime);
format actual_date yymmdd10. actual_datetime datetime19.;
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
;
Hello,
I am receiving following error when I run these code:
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
;
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)
Hello,
I am getting an error:
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
;
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.