topic Re: Select and delete rows in PROC SQL for not complying with the requirements in New SAS User

Select and delete rows in PROC SQL for not complying with the requirements

d6k5d3 — Wed, 10 Oct 2018 08:49:04 GMT

I have a table like the following:

The first two rows do not comply with the requirement as they do not show time. How can I select the 2 rows and delete afterward on SAS?

Thanks in advance?

Re: Select and delete rows in PROC SQL for not complying with the requirements

Kurt_Bremser — Wed, 10 Oct 2018 09:54:28 GMT

Please do not post data in pictures, use data steps with datalines.

A therefore untested solution:

data want;
set have;
if countw(date_time) = 1 then delete;
run;

Re: Select and delete rows in PROC SQL for not complying with the requirements

Reeza — Wed, 10 Oct 2018 15:05:54 GMT

What is the data type of the first column?

@d6k5d3 wrote:

I have a table like the following:

The first two rows do not comply with the requirement as they do not show time. How can I select the 2 rows and delete afterward on SAS?

Thanks in advance?

You could just check the length of it if it's a character and if the length is less than whatever it should be, then delete the record.

Re: Select and delete rows in PROC SQL for not complying with the requirements

d6k5d3 — Wed, 10 Oct 2018 16:07:14 GMT

Hello Kurt,

I am sorry for that.

However, I tried your code. But no rows got deleted.

I imported the Date_time as: informat Date_Time $21. ; format Date_Time $21. ; input Date_Time $;

Any other thoughts?

I appreciate your time. Thank you.

Re: Select and delete rows in PROC SQL for not complying with the requirements

d6k5d3 — Wed, 10 Oct 2018 16:09:53 GMT

Hey Reeza, Thanks!
I imported them as character.

Re: Select and delete rows in PROC SQL for not complying with the requirements

Reeza — Wed, 10 Oct 2018 16:26:36 GMT

@d6k5d3 wrote:
Hey Reeza, Thanks!
I imported them as character.

Did you try the length method I suggested?

if length(dateTime) < 10 then delete;

Re: Select and delete rows in PROC SQL for not complying with the requirements

d6k5d3 — Thu, 11 Oct 2018 00:56:08 GMT

Great! Worked just fine! Thank you very much! 🙂