Desktop productivity for business analysts and programmers

Proc SQL - IF/THEN Conditions

Accepted Solution Solved
Reply
Learner
Posts: 1
Accepted Solution

Proc SQL - IF/THEN Conditions

I need to make a condition where if time is <= 09:00 then 1; if time is > 09:00 and < 17:00 then 2 and if > 17:00 then 3.

Accepted Solutions
Solution
‎11-09-2017 02:38 PM
Super Contributor
Super Contributor
Posts: 260

Re: Proc SQL - IF/THEN Conditions

[ Edited ]
Posted in reply to eduardo_pedrosa

I would guess a CASE statement would get it done, but if all you are doing is setting a flag, I would be tempted to do it in the data step (and this is coming from an SQL guy).

 

 

 

data mytimes;
   input id time time.;
datalines;
1105 08:00
1106 08:30
1107 09:10
1108 10:10
1109 12:56
1110 16:59
1111 17:01
1112 23:45
;
run;

* method 1;
data mytime_flags;
	set mytimes;
	mytimeflag= 0;
	if time <= '09:00't then mytimeflag = 1;
	if time > '09:00't and time < '17:00't then mytimeflag = 2;
	if time >= '17:00't then mytimeflag = 3;
run;

*method 2;
proc sql;
	create table mytime_flags2 as 
	select id, time,
	case 
	when time <= '09:00't then 1
	when time > '09:00't and time < '17:00't then 2
	when time >= '17:00't then 3
	end as mytimeflag
	from mytimes;
quit;

 

 

View solution in original post


All Replies
Solution
‎11-09-2017 02:38 PM
Super Contributor
Super Contributor
Posts: 260

Re: Proc SQL - IF/THEN Conditions

[ Edited ]
Posted in reply to eduardo_pedrosa

I would guess a CASE statement would get it done, but if all you are doing is setting a flag, I would be tempted to do it in the data step (and this is coming from an SQL guy).

 

 

 

data mytimes;
   input id time time.;
datalines;
1105 08:00
1106 08:30
1107 09:10
1108 10:10
1109 12:56
1110 16:59
1111 17:01
1112 23:45
;
run;

* method 1;
data mytime_flags;
	set mytimes;
	mytimeflag= 0;
	if time <= '09:00't then mytimeflag = 1;
	if time > '09:00't and time < '17:00't then mytimeflag = 2;
	if time >= '17:00't then mytimeflag = 3;
run;

*method 2;
proc sql;
	create table mytime_flags2 as 
	select id, time,
	case 
	when time <= '09:00't then 1
	when time > '09:00't and time < '17:00't then 2
	when time >= '17:00't then 3
	end as mytimeflag
	from mytimes;
quit;

 

 

☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 278 views
  • 2 likes
  • 2 in conversation