where and IF conditions

[robertrao]

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;

[art297]

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

[robertrao]

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

[art297]

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.

[Tom]

Code looks fine.  Look at the data.

Try

proc freq ;

tables place*remove / list  ;

format place remove yymon. ;

run;

[robertrao]

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

[PGStats]

Is there a variable called PLACE in your HAVE dataset?

[robertrao]

Hi PG,

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

I corrected it

Sorry abt that

[PGStats]

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.

[Tom · Accepted solution]

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