DATA Step, Macro, Functions and more

Multiple THEN from if/then statement

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

Multiple THEN from if/then statement

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


Accepted Solutions
Solution
‎05-05-2017 07:59 AM
Regular Contributor
Posts: 217

Re: Multiple THEN from if/then statement

Posted in reply to tobyfarms

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

View solution in original post


All Replies
Super User
Posts: 19,822

Re: Multiple THEN from if/then statement

Posted in reply to tobyfarms

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?

Contributor
Posts: 44

Re: Multiple THEN from if/then statement

@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.

Super User
Posts: 19,822

Re: Multiple THEN from if/then statement

Posted in reply to tobyfarms

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. 

Super User
Posts: 19,822

Re: Multiple THEN from if/then statement

Posted in reply to tobyfarms

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

Super User
Posts: 11,343

Re: Multiple THEN from if/then statement

Posted in reply to tobyfarms

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.

Super User
Super User
Posts: 7,060

Re: Multiple THEN from if/then statement

Posted in reply to tobyfarms

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;
Solution
‎05-05-2017 07:59 AM
Regular Contributor
Posts: 217

Re: Multiple THEN from if/then statement

Posted in reply to tobyfarms

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

Contributor
Posts: 44

Re: Multiple THEN from if/then statement

Posted in reply to AhmedAl_Attar

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.

 

Contributor
Posts: 44

Re: Multiple THEN from if/then statement

Posted in reply to 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.

 

statecityfmtnamestartlabelhour2minutehour_0hour_1hour_2hour_3hour_4hour_5hour_6hour_7hour_8hour_9hour_10hour_11hour_12hour_13hour_14
DEDoversunrise13Jul201704:47447000000.78330.78330.78330.78330.78330.783330.783330.783330.783330.78333
DEDoversunset13Jul201719:281928000000000000000

 

 

 

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

01JAN201707:3838000000000.633330.633330.633330.633330.633330.633330.633330.633330.633330.633330.633330.633330.633330.633330.633330.633337
01JAN201717:044000000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.066670.066670.066670.066670.066670.0666717
02JAN201707:3838000000000.633330.633330.633330.633330.633330.633330.633330.633330.633330.633330.633330.633330.633330.633330.633330.633337
02JAN201717:055000000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.000000.083330.083330.083330.083330.083330.0833317

 

I hope this helps,

Ahmed

Regular Contributor
Posts: 217

Re: Multiple THEN from if/then statement

Posted in reply to tobyfarms

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
Contributor
Posts: 44

Re: Multiple THEN from if/then statement

Posted in reply to AhmedAl_Attar

@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.

 

statecityfmtnamestartlabelhour2minutehour_2hour_3hour_4hour_5hour_6
DEDoversunrise13Jul201704:474470000.7833333020.783333302
DEDoversunset13Jul201719:2819280.466666460.466666460.466666460.466666460.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;

 

Contributor
Posts: 44

Re: Multiple THEN from if/then statement

Posted in reply to 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. 

 

statecityfmtnamestartlabelhour2minutehour_5hour_6hour_7hour_8
MDBaltimoresunrise07Jul201704:474470.7833333020.7833333020.7833333020.783333302
MDBaltimoresunset07Jul201719:3619360.5999999050.5999999050.5999999050.599999905
Regular Contributor
Posts: 217

Re: Multiple THEN from if/then statement

Posted in reply to tobyfarms

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

Contributor
Posts: 44

Re: Multiple THEN from if/then statement

Posted in reply to AhmedAl_Attar

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: 

 

statecityfmtnamestartlabelhour2minutehour_0hour_1hour_2hour_3hour_4hour_5hour_6hour_7hour_8hour_9hour_10hour_11hour_12hour_13hour_14hour_15hour_16hour_17hour_18hour_19hour_20hour_21hour_22hour_23
MDBaltimoresunrise17Jan201707:24724111111110.40000.40000.40000.40000.40000.40000.40000.40000.40000.40000.40000.40000.40000.40000.40000.4000
MDBaltimoresunset17Jan201717:1017100.16670.16670.16670.16670.16670.16670.16670.16670.16670.16670.16670.16670.16670.16670.16670.16670.16671111111

 

Want:

 

statecityfmtnamestartlabelhour2minutehour_0hour_1hour_2hour_3hour_4hour_5hour_6hour_7hour_8hour_9hour_10hour_11hour_12hour_13hour_14hour_15hour_16hour_17hour_18hour_19hour_20hour_21hour_22hour_23
MDBaltimoresunrise17Jan201707:24724111111110.40000.40000.40000.40000.40000.40000.40000.40000.40000.4000111111
MDBaltimoresunset17Jan201717:1017100.16670.16670.16670.16670.16670.16670.16670.166711111111110.16670.16670.16670.16670.16670.1667
☑ This topic is solved.

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

Discussion stats
  • 15 replies
  • 665 views
  • 1 like
  • 5 in conversation