topic Conditioning a record's inclusion by date range in SAS Programming

Conditioning a record's inclusion by date range

newtriks — Fri, 21 Jan 2022 20:53:01 GMT

DATA xlmergM;
	SET xlmergM;
	KEEP MRN date_of_visit tested_for_hcv hcv_result;
	FORMAT date_of_visit DTDATE9.;
	IF tested_for_hcv="Yes";
*	IF date_of_visit  >= '01dec2021'd;
	IF '01dec2021'd <= date_of_visit <='31dec2021'd;
RUN;

Data was uploaded from Excel. Tried sorting with date_of_visit in mm/dd/yyyy format and got nowhere - couldn't even find a thread using this format. Converted to that in the code above and have tried several suggestions from older threads and none seem to work. Tried "between" syntax several times and just spun my wheels. Any help would be appreciated. Thanks!

Re: Conditioning a record's inclusion by date range

PaigeMiller — Fri, 21 Jan 2022 21:00:10 GMT

If you have numeric SAS dates (is the variable DATE_OF_VISIT numeric?) then formats are irrelevant here. And if its not numeric, or not a SAS date value, then it won't work at all.

It would help if you showed us a portion of the data you are using in SAS (not in Excel). The best way to show us the SAS data set is as SAS data step code (instructions).

Also, see this: https://support.sas.com/resources/papers/proceedings/proceedings/sugi29/068-29.pdf

Re: Conditioning a record's inclusion by date range

newtriks — Fri, 21 Jan 2022 21:11:23 GMT

Yes, the variable is numeric per Proc Contents. Date_of_visit is (obviously) in the 2d column. Is this enough?

E100177

01JAN1960

Yes

Negative

E1006201

01JAN1960

Yes

Negative

E100678

01JAN1960

Yes

Negative

E1012271

01JAN1960

Yes

Negative

E1012628

01JAN1960

Yes

Negative

E1017103

01JAN1960

Yes

Negative

E101749

01JAN1960

Yes

Negative

E1024127

01JAN1960

Yes

Negative

E1026100

01JAN1960

Yes

Negative

E1027774

01JAN1960

Yes

Negative

E1028040

01JAN1960

Yes

Negative

E103659

01JAN1960

Yes

Negative

E10392

01JAN1960

Yes

Negative

E10446

01JAN1960

Yes

Negative

E104597

01JAN1960

Yes

Negative

E105103

01JAN1960

Yes

Negative

E1055709

01JAN1960

Yes

Negative

E105751

01JAN1960

Yes

Negative

E105754

01JAN1960

Yes

Negative

E10671

01JAN1960

Yes

Negative

E107871

01JAN1960

Yes

Negative

E108743

01JAN1960

Yes

Negative

E108806

01JAN1960

Yes

Negative

E1090914

01JAN1960

Yes

Negative

E1091678

01JAN1960

Yes

Negative

Re: Conditioning a record's inclusion by date range

PaigeMiller — Fri, 21 Jan 2022 21:47:53 GMT

DATE_OF_VISIT is numeric, and appears to be a date value but a date/time format has been applied, or the numbers imported from Excel are gibberish. In this case (and in every case), following the instructions for presenting the data would lead to a definitive answer (and is to your benefit as you will get faster and better answers by providing the data in the requested format). As presented above, its not really clear to me what has happened.

Re: Conditioning a record's inclusion by date range

newtriks — Mon, 24 Jan 2022 18:34:59 GMT

Ok, this is far more complicated than my paygrade but I'll do whatever is necessary. Bear with me because a lot of basic questions are incoming.

For the first bit of code, I entered

PROC SQL;
CREATE TABLE xlmergZ AS
select Date_of_Visit
	 into :varlist separated by ' '
   from xlmergM;
QUIT;

... and got back the column of entries under the variable I asked for. So far so good I think.

The next code string in the example you cited is utter Greek to me, even with a SAS book at my side to refer to. Here's what it says:

select case type

when 'num' then

case

when missing(format) then cats(Name,':32.')

else cats(Name,':',format)

end

else cats(Name,':$',length,'.')

end

into :inputlist separated by ' '

from dictionary.columns

where libname="&lib"

and memname="&dsn"

;

I entered this:

PROC SQL;

CREATE TABLE xlmergY AS

select case Date_of_Visit

when 'num' then

case

when missing(format) then cats(Name,':32.')

else cats(Name,':',format)

end

else cats(Name,':$',length,'.')

end

into :inputlist separated by ' '

from dictionary.columns

where libname="&lib"

and memname="&dsn"

;

LOG returned the error that it did not recognize my variable date_of_visit.

I am sorry to require so much hand-holding but I am utterly lost. I appreciate that you need this info to answer my question, and I am trying, but was already having trouble with the elementary code to upload a file in another format, and now I'm trying to write SQL code in order to get an answer to a far simpler question.