Help using Base SAS procedures

where and IF conditions

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,041
Accepted Solution

where and IF conditions

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;


Accepted Solutions
Solution
‎08-24-2013 11:08 PM
Super User
Super User
Posts: 7,076

Re: where and IF conditions

Posted in reply to robertrao

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

View solution in original post


All Replies
PROC Star
Posts: 7,492

Re: where and IF conditions

Posted in reply to robertrao

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


Super Contributor
Posts: 1,041

Re: where and IF conditions

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

PROC Star
Posts: 7,492

Re: where and IF conditions

Posted in reply to robertrao

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.

Super User
Super User
Posts: 7,076

Re: where and IF conditions

Posted in reply to robertrao

Code looks fine.  Look at the data.

Try

proc freq ;

tables place*remove / list  ;

format place remove yymon. ;

run;

Super Contributor
Posts: 1,041

Re: where and IF conditions

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

                                  

Respected Advisor
Posts: 4,934

Re: where and IF conditions

Posted in reply to robertrao

Is there a variable called PLACE in your HAVE dataset?

PG
Super Contributor
Posts: 1,041

Re: where and IF conditions

Hi PG,

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

I corrected it

Sorry abt that

Respected Advisor
Posts: 4,934

Re: where and IF conditions

Posted in reply to robertrao

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.

PG
Solution
‎08-24-2013 11:08 PM
Super User
Super User
Posts: 7,076

Re: where and IF conditions

Posted in reply to robertrao

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 245 views
  • 4 likes
  • 4 in conversation