BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tobyfarms
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
AhmedAl_Attar
Rhodochrosite | Level 12

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

15 REPLIES 15
Reeza
Super User

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
Fluorite | Level 6

@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
Super User

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
Super User

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
Super User

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
Super User Tom
Super User

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
Rhodochrosite | Level 12

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
Fluorite | Level 6

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
Fluorite | Level 6

@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

AhmedAl_Attar
Rhodochrosite | Level 12

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
Fluorite | Level 6

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

 

tobyfarms
Fluorite | Level 6

@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
AhmedAl_Attar
Rhodochrosite | Level 12

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
Fluorite | Level 6

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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