Contributor
Posts: 56

# Arrays - Nested do loops to compare two rows

[ Edited ]

Hello.

I have a dataset where each row is different but has a matching date/city/state.  2 rows per for each date (1 sunrise & 1 sunset).  I am trying to create by-hour calculations for sunrise and sunset over the course of a 24hr period. The current code runs but what I now need is to have the 'sunset' factor begin where the sunrise ends.

 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 DE Wilmington sunrise 06Apr2017 05:38 5 38 1 1 1 1 1 0.633333206 0.633333206 0.633333206 0.633333206 0.633333206 0.633333206 0.633333206 0.633333206 0.633333206 0.633333206 0.633333206 1 1 1 1 1 1 1 1 DE Wilmington sunset 06Apr2017 18:32 18 32 0.533333302 0.533333302 0.533333302 0.533333302 0.533333302 0.533333302 0.533333302 0.533333302 1 1 1 1 1 1 1 1 1 1 1 0.533333302 0.533333302 0.533333302 0.533333302 0.533333302

**Here is my current code, thanks to @AhmedAl_Attar.

data want(drop=i);
set have;
array hours {24} 4 hour_0-hour_23;
format hour_: BEST8.4;

/* Set all Hours to 0 */
do i=1 to dim(hours);
hours[i]=1;
end;

if (fmtname='sunrise') then
/* Assign the Subsequent Hours the factors (minutes/60) */
do i=(hour+2) to (12+hour-1);
hours[i]=minute/60;
end;
else if (fmtname='sunset') then
do;
/* Assign the Hours prior 8:00 am the factors (minutes/60) */
do i=8 to 1 by -1;
hours[i]=minute/60;
end;
/* Assign the Subsequent Hours the factors (minutes/60) */
do i=(hour+2) to dim(hours);
hours[i]=minute/60;
end;

end;
output;
run;

Super User
Posts: 13,358

## Re: Arrays - Nested do loops to compare two rows

Posted in reply to tobyfarms

Please provide data in the form of a data step. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

It will also help to post what the desired output should look like. I am not real sure if the two rows posted are what you are starting with or not.

Contributor
Posts: 56

## Re: Arrays - Nested do loops to compare two rows

@ballardw. Hello - I have the data step format listed, its below the question and display.
PROC Star
Posts: 1,603

## Re: Arrays - Nested do loops to compare two rows

@ballardw I agree with you Sir. First of all, i am too lazy to open attachments no matter how excited i am when there is a prospect for coding. Second of all, If i try to open any external attachment from my corporate environment, it's pretty certain i may have to face severe consequences. So it's best for anybody to post in the form of HAVE and WANT not necessarily as a datastep but atleast as clear and smaller lines with a few bullet points explaining the convert logic and requirement.

Super Contributor
Posts: 278

## Re: Arrays - Nested do loops to compare two rows

Posted in reply to tobyfarms

Hi,

I think this new version of the code, will give you what you are looking for,

DATA have;
Length
state \$2
city \$10
fmtname \$7
start 8
label \$5
hour 4
minute 4;
INPUT state city fmtname start :date9. label hour minute;
FORMAT start date9. label \$5.;
DATALINES;
MD Baltimore sunrise 17Jan2017 07:24 7 24
MD Baltimore sunset 17Jan2017 17:10 17 10
DE Dover sunrise 13JUL2017 04:47 4 47
DE Dover sunset 13JUL2017 19:28 19 28
;
RUN;

/* Sort the Data in preparation for consalidation into single line per state/city/date */
PROC SORT data=have out=have_srtd;
BY state city start label hour;
RUN;

/* --------------------------------------------------------- */
/* The step below will:                                      */
/* - Consolidate data into single record per state/city/date */
/* - Collect numbers & calculate factor for sunrise & sunset */
/* - Output a record for sunrise and record for sunset.      */
/* --------------------------------------------------------- */
DATA want(KEEP=state city fmtname start label hour_;

ARRAY init_hrs [24] _temporary_ (1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1);

LENGTH    state \$2
city \$10
fmtname \$7
start 8
label \$5
hour 4
minute 4;

LENGTH     sunrise \$5
sunset \$5
rise_hr 4
set_hr 4
rise_min 4
set_min 4
rise_factor 8
set_factor 8;

ARRAY hours {24} 4 hour_0-hour_23;
FORMAT hour_: BEST8.4;

RETAIN init_rb8_str hr1_addr sunrise rise_hr set_hr rise_min rise_factor;

IF (_n_ = 1) THEN
DO;
/* Get the String representation of the array elements */
init_rb8_str = put (peekclong (addrlong(init_hrs[1]), 192), \$192.);

/* Get the memory address of the first element of the hours array */
END;

/* - Consolidate data into single record per state/city/date */
/* - Collect numbers & calculate factor for sunrise & sunset */
DO UNTIL (last.start);
SET have_srtd;
BY state city start label hour;

if (lowcase(fmtname)='sunrise') then
do;
/* Process SunRise Time */
sunrise = label;
rise_hr  = hour;
rise_min = minute;
rise_factor = rise_min/60;
end;
else
do;
/* Process SunSet Time */
sunset = label;
set_hr  = hour;
set_min = minute;
set_factor = set_min/60;
end;
END;

/* ---------------------------------------------------- */
/* - Output a record for sunrise and record for sunset. */
/* ---------------------------------------------------- */

* --- Output the SunRise Record --- ;
fmtname = 'sunrise';
label   = sunrise;

/* Set all Hours to 1 in a single command */
CALL POKELONG (init_rb8_str, hr1_addr, 192);

/* Assign the SunRise factor hours */
do i=(rise_hr+2) to (set_hr+1);
hours[i]=rise_factor;
end;
OUTPUT;

* --- Output the SunSet Record --- ;
fmtname = 'sunset';
label   = sunset;

/* Reset all Hours to 1 in a single command */
CALL POKELONG (init_rb8_str, hr1_addr, 192);

/* Deals with the hours prior to sunRise */
do i=1 to (rise_hr+1);
hours[i]=set_factor;
end;

/* Deals with the hours after sunSet */
do i=(set_hr+2) to dim(hours);
hours[i]=set_factor;
end;
OUTPUT;

RUN;

Obs state city fmtname start label 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
1 DE Dover sunrise 13JUL2017 04:47 1 1 1 1 1 0.783333 0.783333 0.783333 0.783333 0.783333 0.783333 0.783333 0.783333 0.783333 0.783333 0.783333 0.783333 0.783333 0.783333 0.783333 1 1 1 1
2 DE Dover sunset 13JUL2017 19:28 0.466666 0.466666 0.466666 0.466666 0.466666 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0.466666 0.466666 0.466666 0.466666
3 MD Baltimore sunrise 17JAN2017 07:24 1 1 1 1 1 1 1 1 0.4 0.4 0.4 0.4 0.4 0.4 0.4 0.4 0.4 0.4 1 1 1 1 1 1
4 MD Baltimore sunset 17JAN2017 17:10 0.166667 0.166667 0.166667 0.166667 0.166667 0.166667 0.166667 0.166667 1 1 1 1 1 1 1 1 1 1 0.166667 0.166667 0.166667 0.166667 0.166667 0.166667
Discussion stats
• 4 replies
• 191 views
• 1 like
• 4 in conversation