Multiple THEN from if/then statement

[tobyfarms]

How to create multiple THEN results from an if/then statement?

What I have does not compute. 

if fmtname='sunrise' and hour >hour_2 then factor_1-factor_2 =0;

else factor_1=minute/60-factor_2=minute/60.;

[Reeza]

Your mistake is in your assignments. The statements after the THEN don't make sense.

if fmtname='sunrise' and hour >hour_2 then factor_1-factor_2 =0;

else factor_1=minute/60-factor_2=minute/60.;

I've highlighted the issues in red, can you explain what you were trying to do here?

[tobyfarms]

@ReezaSure thing,   I have hour buckets such hour_0 through hour_23

if '00:00' <= label <='00:59' then Hour_0 = 0;

else if '01:00' <= label <='01:59' then Hour_1 = 1;

else if '02:00' <= label <='02:59' then Hour_2 = 2;

else if '03:00' <= label <='03:59' then Hour_3 = 3;

I want to create a 'Factor' field by determining if a specific hour is greater than Hour_2, hour_3, etc.  If so, then give me the minutes for that hour those fields.

so if the time is 2:56, then hours 1-2 will be 0, and 3 through 23 will =56/60.

Sorry if this doesn't make sense.

[Reeza]

Verify if you have SAS times or if your fields are genuinely character. If you convert them to times you can use math operations instead of the multiple IF conditions. 

[Reeza]

If you want to post sample data and expected output, we can probably suggest some more efficient methods. 

To answer your original question, the assignments/calculations after the THEN were not correct. Your usage below is correct. Another way of considering it, is the code after THEN should be stand alone, ie it should work as a SAS line on it's own

[ballardw]

so if the time is 2:56, then hours 1-2 will be 0, and 3 through 23 will =56/60.

Does not make sense as your examples have the Hour_x variables as 0, 1, 2 etc. 56/60 doesn't fit at all.

If you are setting multiple variables with such ranges then I think you don't want the If/then/else.

Also you have swithched from Hour values to Label. So which are you actually comparing?

Provide an example of the input data and the desired result for that input data.

I think you may only need to show 6 hours or so.

Note that text comparisons involving < or > are also very unlikely to do what you are thinking.

[Tom]

To execute mutliple statements just use DO/END block.

if fmtname='sunrise' and hour >hour_2 then do;
  factor_1=0;
  factor_2=0;
end;
else do;
  factor_1=minute/60;
  factor_2=minute/60;
end;

[AhmedAl_Attar · Accepted solution]

Is this what you are looking for ?

data have;
    length    date 8
            time    $5
            minute    4;
    input    date :date9. time minute;
    format    date date9. time $5.;
datalines;
01JAN2017 07:38 38
01JAN2017 17:04 04
02JAN2017 07:38 38
02JAN2017 17:05 05
;
run;

data want(drop=i);
    set have;
    array    hours {24} 4 hour_0-hour_23;
    hour = input(scan(time,1,':'),2.);
    
    /* Set The Prior Hours to 0 */
    do i=(hour+1) to 1 by -1;
        hours[i] = 0;
    end;
    
    /* Assign the Subsequent Hours the factors (minutes/60) */
    do i=(hour+2) to dim(hours);
        hours[i] = minute/60;
    end;
    output;
run;

Obs	date	time	minute	hour_0	hour_1	hour_2	hour_3	hour_4	hour_5	hour_6	hour_7	hour_8	hour_9	hour_10	hour_11	hour_12	hour_13	hour_14	hour_15	hour_16	hour_17	hour_18	hour_19	hour_20	hour_21	hour_22	hour_23	hour
1	01JAN2017	07:38	38	0	0	0	0	0	0	0	0	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	7
2	01JAN2017	17:04	4	0	0	0	0	0	0	0	0	0.00000	0.00000	0.00000	0.00000	0.00000	0.00000	0.00000	0.00000	0.00000	0.00000	0.06667	0.06667	0.06667	0.06667	0.06667	0.06667	17
3	02JAN2017	07:38	38	0	0	0	0	0	0	0	0	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	7
4	02JAN2017	17:05	5	0	0	0	0	0	0	0	0	0.00000	0.00000	0.00000	0.00000	0.00000	0.00000	0.00000	0.00000	0.00000	0.00000	0.08333	0.08333	0.08333	0.08333	0.08333	0.08333	17

I hope this helps,

Ahmed

[tobyfarms]

Thanks @AhmedAl_Attar ..

I had to make a minor adjustment to what the code you provided (mainly formatting) but it does work in building out factors.  Thank you for providing this.

[tobyfarms]

@AhmedAl_Attar

Is there a way to add one more layer to this do loop?   I will need to add If/Then statements back in the loop, based on FMTNAME.

If fmtname='sunrise' then do i=(hour+1) to 1 by -1;  (giving me a factor for every hour >= label.

If fmtname='sunset'  then do..... (will need a result factor every hour <= label.

state	city	fmtname	start	label	hour2	minute	hour_0	hour_1	hour_2	hour_3	hour_4	hour_5	hour_6	hour_7	hour_8	hour_9	hour_10	hour_11	hour_12	hour_13	hour_14
DE	Dover	sunrise	13Jul2017	04:47	4	47	0	0	0	0	0	0.7833	0.7833	0.7833	0.7833	0.7833	0.78333	0.78333	0.78333	0.78333	0.78333
DE	Dover	sunset	13Jul2017	19:28	19	28	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0

Solved Items(Previous)
data want(drop=i);
    set have;
    array    hours {24} 4 hour_0-hour_23;
    hour = input(scan(time,1,':'),2.);
    
    /* Set The Prior Hours to 0 */
    do i=(hour+1) to 1 by -1;
        hours[i] = 0;
    end;
    
    /* Assign the Subsequent Hours the factors (minutes/60) */
    do i=(hour+2) to dim(hours);
        hours[i] = minute/60;
    end;
    output;
run;

  Obs date time minute hour_0 hour_1 hour_2 hour_3 hour_4 hour_5 hour_6 hour_7 hour_8 hour_9 hour_10 hour_11 hour_12 hour_13 hour_14 hour_15 hour_16 hour_17 hour_18 hour_19 hour_20 hour_21 hour_22 hour_23 hour 1 2 3 4

01JAN2017	07:38	38	0	0	0	0	0	0	0	0	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	7
01JAN2017	17:04	4	0	0	0	0	0	0	0	0	0.00000	0.00000	0.00000	0.00000	0.00000	0.00000	0.00000	0.00000	0.00000	0.00000	0.06667	0.06667	0.06667	0.06667	0.06667	0.06667	17
02JAN2017	07:38	38	0	0	0	0	0	0	0	0	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	0.63333	7
02JAN2017	17:05	5	0	0	0	0	0	0	0	0	0.00000	0.00000	0.00000	0.00000	0.00000	0.00000	0.00000	0.00000	0.00000	0.00000	0.08333	0.08333	0.08333	0.08333	0.08333	0.08333	17

I hope this helps,

Ahmed

[AhmedAl_Attar]

Is this what you are looking for ?

data have;
    length    state $2
        city $5        
        fmtname $7
        start 8
        label $5
        minute 4;
    input state city fmtname start :date9. label minute;
    format start date9. label $5.;
    datalines;
DE Dover sunrise 13JUL2017 04:47 4
DE Dover sunset 13JUL2017 19:28 19
;
run;

data want(drop=i);
    set have;
    array hours {24} 4 hour_0-hour_23;
    hour=input(scan(label, 1, ':'), 2.);
    
    /* Set all Hours to 0 */
    do i=1 to dim(hours);
        hours[i]=0;
    end;

    if (fmtname='sunrise') then
        /* Assign the Subsequent Hours the factors (minutes/60) */
        do i=(hour+2) to dim(hours);
            hours[i]=minute/60;
        end;
    else if (fmtname='sunset') then
        /* Assign the Prior Hours the factors (minutes/60) */
        do i=(hour) to 1 by -1;
            hours[i]=minute/60;
        end;
    
    output;
run;

Obs	state	city	fmtname	start	label	minute	hour_0	hour_1	hour_2	hour_3	hour_4	hour_5	hour_6	hour_7	hour_8	hour_9	hour_10	hour_11	hour_12	hour_13	hour_14	hour_15	hour_16	hour_17	hour_18	hour_19	hour_20	hour_21	hour_22	hour_23	hour
1	DE	Dover	sunrise	13JUL2017	04:47	4	0.00000	0.00000	0.00000	0.00000	0.00000	0.06667	0.06667	0.06667	0.06667	0.06667	0.06667	0.06667	0.06667	0.06667	0.06667	0.06667	0.06667	0.06667	0.06667	0.066667	0.066667	0.066667	0.066667	0.066667	4
2	DE	Dover	sunset	13JUL2017	19:28	19	0.31667	0.31667	0.31667	0.31667	0.31667	0.31667	0.31667	0.31667	0.31667	0.31667	0.31667	0.31667	0.31667	0.31667	0.31667	0.31667	0.31667	0.31667	0.31667	0.000000	0.000000	0.000000	0.000000	0.000000	19

[tobyfarms]

@AhmedAl_AttarYes thank you. 

I have another layer.   on any particular date (using DE Dover 13JULY 2017), i'm showing a factor for both sunrise and sunset.   

Hours 5-6 should only show a 'sunrise' factor since it is after sunrise and should reflect '0' in the sunset for that same period.

state	city	fmtname	start	label	hour2	minute	hour_2	hour_3	hour_4	hour_5	hour_6
DE	Dover	sunrise	13Jul2017	04:47	4	47	0	0	0	0.783333302	0.783333302
DE	Dover	sunset	13Jul2017	19:28	19	28	0.46666646	0.46666646	0.46666646	0.46666646	0.46666646

I was working on adding another layer of 'AND' statements to your code, but this does not work.  

/* Assign the Subsequent Hours the factors (minutes/60) */

if (fmtname='sunrise' and city=city and state=state and start<>start*/) then

do i=(hour+2) to dim(hours);

hours[i]=minute/60;

[tobyfarms]

@AhmedAl_Attar Thank you but it does not fix the issue where there is a value for sunrise and sunset both display results. 

Where 'sunrise' is NE to 0,, then 'Sunset (second row) should have an output of 0.    I believe that I may have to do this in a seperate step. 

state	city	fmtname	start	label	hour2	minute	hour_5	hour_6	hour_7	hour_8
MD	Baltimore	sunrise	07Jul2017	04:47	4	47	0.783333302	0.783333302	0.783333302	0.783333302
MD	Baltimore	sunset	07Jul2017	19:36	19	36	0.599999905	0.599999905	0.599999905	0.599999905

[AhmedAl_Attar]

Hi,
I think the best way to understand exactly what you want, is by you providing me a complete sample input and desired output.
i.e.

Sample Input: (Have)

state	city	fmtname	start	label	hour	minute
MD	Baltimore	sunrise	7-Jul-17	4:47	4	47
MD	Baltimore	sunset	7-Jul-17	19:36	19	36

Expected Output: (Want)

state

city

fmtname

start

label

hour

minute

hour_0

hour_1

hour_2

hour_3

hour_4

hour_5

hour_6

hour_7

hour_8

hour_9

hour_10

hour_11

hour_12

hour_13

hour_14

hour_15

hour_16

hour_17

hour_17

hour_18

hour_19

hour_20

hour_21

hour_22

hour_23

MD

Baltimore

sunrise

7-Jul-17

4:47

4

47

MD

Baltimore

sunset

7-Jul-17

19:36

19

36

Thanks,

Ahmed

[tobyfarms]

Sure thing @AhmedAl_Attar.  I am trying to show values for daylight and night hours within a 24hr period.  The current results has some overlap between hours_8 and hours_16, which would make it appear as if both daylight and night were happening within the same hour.

Have: 

state	city	fmtname	start	label	hour2	minute	hour_0	hour_1	hour_2	hour_3	hour_4	hour_5	hour_6	hour_7	hour_8	hour_9	hour_10	hour_11	hour_12	hour_13	hour_14	hour_15	hour_16	hour_17	hour_18	hour_19	hour_20	hour_21	hour_22	hour_23
MD	Baltimore	sunrise	17Jan2017	07:24	7	24	1	1	1	1	1	1	1	1	0.4000	0.4000	0.4000	0.4000	0.4000	0.4000	0.4000	0.4000	0.4000	0.4000	0.4000	0.4000	0.4000	0.4000	0.4000	0.4000
MD	Baltimore	sunset	17Jan2017	17:10	17	10	0.1667	0.1667	0.1667	0.1667	0.1667	0.1667	0.1667	0.1667	0.1667	0.1667	0.1667	0.1667	0.1667	0.1667	0.1667	0.1667	0.1667	1	1	1	1	1	1	1

Want:

state	city	fmtname	start	label	hour2	minute	hour_0	hour_1	hour_2	hour_3	hour_4	hour_5	hour_6	hour_7	hour_8	hour_9	hour_10	hour_11	hour_12	hour_13	hour_14	hour_15	hour_16	hour_17	hour_18	hour_19	hour_20	hour_21	hour_22	hour_23
MD	Baltimore	sunrise	17Jan2017	07:24	7	24	1	1	1	1	1	1	1	1	0.4000	0.4000	0.4000	0.4000	0.4000	0.4000	0.4000	0.4000	0.4000	0.4000	1	1	1	1	1	1
MD	Baltimore	sunset	17Jan2017	17:10	17	10	0.1667	0.1667	0.1667	0.1667	0.1667	0.1667	0.1667	0.1667	1	1	1	1	1	1	1	1	1	1	0.1667	0.1667	0.1667	0.1667	0.1667	0.1667