topic Re: Arrays - Nested do loops to compare two rows in SAS Programming

Arrays - Nested do loops to compare two rows

tobyfarms — Wed, 10 May 2017 18:41:19 GMT

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;

Re: Arrays - Nested do loops to compare two rows

ballardw — Wed, 10 May 2017 18:15:18 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: Arrays - Nested do loops to compare two rows

tobyfarms — Wed, 10 May 2017 18:17:22 GMT

@ballardw. Hello - I have the data step format listed, its below the question and display.

Re: Arrays - Nested do loops to compare two rows

novinosrin — Wed, 10 May 2017 18:33:46 GMT

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

Re: Arrays - Nested do loops to compare two rows

AhmedAl_Attar — Fri, 12 May 2017 14:19:22 GMT

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 */
       hr1_addr = addrlong(hours[1]);
   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