topic Re: How do I calculate julian dates from month-year data organized in columns in SAS Programming

How do I calculate julian dates from month-year data organized in columns

bmac1 — Wed, 15 Jul 2020 12:53:53 GMT

Hi SAS community,

I have a data set of observations for different months in different years (spanning over 100 years), with the monthly data arranged in 12 columns for each year as below:

Year jan feb mar apr may jun jul aug sep oct nov dec

1856 0.235 0.168 0.24 0.159 0.211 0.234 0.247 0.224 0.291 0.141 0.152 0.246

1857 0.231 -0.042 -0.058 0.024 -0.016 0.117 0.149 0.014 0.036 -0.117 -0.152 -0.266

1858 -0.204 -0.297 -0.054 0.228 0.106 -0.023 -0.144 -0.055 0.087 0.165 0.291 0.139

I want to convert the year-month data into a single long time series of julian dates, so that the observation of the first month in the first year (1856) gets assigned a julian date = 1856 + (0.5/12) = 1856.042, and so on. The output file should have the new date variable in its own column, and the corresponding observation for that julian date, as below:

1856.041 0.235

1856.125 0.168

1856.208 0.24

...etc.

Has anyone some suggestions or code to do this (in SAS version 9)?

Thanks for help.

Re: How do I calculate julian dates from month-year data organized in columns

PaigeMiller — Wed, 15 Jul 2020 13:03:38 GMT

/* UNTESTED CODE */
data want;
    set have;
    array x(*) jan--dec;
    do i=1 to dim(x);
        juldate = juldate(mdy(i,1,year));
        value=x(i);
        output;
     end;
     keep juldate value;
     format juldate julian7.;
run;

Re: How do I calculate julian dates from month-year data organized in columns

bmac1 — Wed, 15 Jul 2020 13:55:00 GMT

Hi,
Thank you for a quick response - so much appreciated!
I just tried the code. However, I realize it is not quite what I had in mind...
I need to have the date as a decimal date (centred on the middle of each month) so not a real julian day. Sorry, I should have made that clearer in the first post. Every month (centred on the middle of the month so for example January would get a decimal value of 0.5/12 = 0.042) should be expressed as a fraction of the year, and accumulate during the year to a whole year, so the next year starts again with a decimal value. Like this...
1856

0.792

1856.792

1856

0.875

1856.875

1856

0.958

1856.958

1857

0.042

1857.042

1857

0.125

1857.125

1857

0.208

1857.208

Is there a way to create a decimal year value like this?

Re: How do I calculate julian dates from month-year data organized in columns

ballardw — Wed, 15 Jul 2020 14:30:22 GMT

For not standard values such as below, please indicate the actual date each is supposed to represent:

1856

0.792

1856.792

1856

0.875

1856.875

None of those resemble a Julian date in any form. Julian dates are YYDDD or YYYYDDD where Y is year and DDD is a 3-digit number of the day in a year.

I think you may also want to include some other months worked out just to make sure we understand how you are mangling month.

Re: How do I calculate julian dates from month-year data organized in columns

PaigeMiller — Wed, 15 Jul 2020 14:36:30 GMT

/* UNTESTED CODE */
data want;
    set have;
    array x(*) jan--dec;
    do i=1 to dim(x);
        juldate = year+(i-0.5)/12;
        value=x(i);
        output;
     end;
     keep juldate value;
run;

Re: How do I calculate julian dates from month-year data organized in columns

bmac1 — Wed, 15 Jul 2020 22:20:00 GMT

Hi,

This worked perfectly! Thank you so much for your help.

Re: How do I calculate julian dates from month-year data organized in columns

bmac1 — Wed, 15 Jul 2020 22:24:27 GMT

Hi,

Thank you for replying. What I need is not really a julian date, but a decimal year date. Sorry I did not make that clear enough in the original post. In the meantime another community member has solved the question. Please see the other posts in this thread for information.