topic Re: where and IF conditions in SAS Procedures

where and IF conditions

robertrao — Sat, 24 Aug 2013 16:09:21 GMT

Hi,

I have two variables and place is beside remove in the dataset i am puliing data from

i wanted

place can be between May2013-June2013 but remove in JUNE only

I get zero records in the datasets i am creating even though it says there are 900 records with the dates i specified?

is my approach correct?

%let start =01MAY2013;
%let end =31MAY2013;

%let start2 =01JUN2013;
%let end2 =30JUN2013;

DATA JUNE_out_1 JUNE_IN_1 MAY_IN_1;
SET HAVE(keep=id PLACE REMOVE);
WHERE ("&start"D <=PLACE<= "&end2"D) and ("&start2"D <=REMOVE <= "&end2"D);

IF ("&start2"D <=REMOVE<= "&end2"D) THEN DO;
FLAG="OUT";
OUTPUT JUNE_out_1;
END;

IF ("&start2"D <=PLACE <= "&end2"D)THEN DO;
FLAG="IN";
OUTPUT JUNE_IN_1;
END;

IF ("&start"D <=PLACE <= "&end"D)THEN DO;
FLAG="IN";
OUTPUT MAY_IN_1;
END;
RUN;

Re: where and IF conditions

art297 — Sat, 24 Aug 2013 20:45:03 GMT

Do you expect records to meet both of those conditions? i.e.:

WHERE ("&start"D <=PLACE<= "&end2"D) and ("&start2"D <=REMOVE <= "&end2"D);

If not, possibly you should have used:

WHERE ("&start"D <=PLACE<= "&end2"D) or ("&start2"D <=REMOVE <= "&end2"D);

Re: where and IF conditions

robertrao — Sat, 24 Aug 2013 21:20:35 GMT

Hi Art,

Thnaks for the reply.

"Place " can be in may or june but "remove" has to be in june only....

so i think it has to meet both the conditions

WHERE ("&start"D <=PLACE<= "&end2"D) and ("&start2"D <=REMOVE <= "&end2"D);

that criteria is because some records might have a "place" in May and remove in "july".........in this scenario i want only the "place" value

if i use OR condition then a person who had a "place " in January but remove in "June" comes up which we dont want!!!

the second problem is that with the code i provided is that i am getting zero records for all the datasets i created!!!!

could you please tell where i went wrong???

Thanks

Re: where and IF conditions

art297 — Sat, 24 Aug 2013 21:40:28 GMT

I think you have to let the forum know what your rules actually are and provide some sample data (including the values and file assignment you want to achieve).

Since you currently aren't getting any records, either none of your records meet your where condition or, if any do, they don't meeting your two if conditions.

I would remove the where for now and simply expand your two if conditions to meet whatever your criteria actually are.

Re: where and IF conditions

Tom — Sat, 24 Aug 2013 21:43:05 GMT

Code looks fine. Look at the data.

Try

proc freq ;

tables place*remove / list ;

format place remove yymon. ;

run;

Re: where and IF conditions

robertrao — Sat, 24 Aug 2013 22:02:47 GMT

Hi,

With only using the where condition i created only one dataset and did a proc freq on that and the result is as floows!!!!

                                                                                           Cumulative    Cumulative
              PLACE    REMOVE    Frequency     Percent     Frequency      Percent

              *******         *******                     901      100.00            901              100.00

Re: where and IF conditions

PGStats — Sun, 25 Aug 2013 01:48:36 GMT

Is there a variable called PLACE in your HAVE dataset?

Re: where and IF conditions

robertrao — Sun, 25 Aug 2013 01:56:46 GMT

Hi PG,

the INSTANT is the PLACE ariable....I mistakenly wrote it as INSTANT

I corrected it

Sorry abt that

Re: where and IF conditions

PGStats — Sun, 25 Aug 2013 02:45:16 GMT

Do you use a database to store your datasets? The formating of your date variables as asterisks suggest that they somehow were transformed into datetime values which puts them outside the range of date numbers. Just a guess.

Re: where and IF conditions

Tom — Sun, 25 Aug 2013 03:08:41 GMT

So it looks like your variables are DATETIME instead of DATE.

You either change the literals in your conditions to datetime literals or use the DATEPART() function.

WHERE ("&start"D <=datepart(PLACE)<= "&end2"D) and ("&start2"D <=datepart(REMOVE) <= "&end2"D);