BookmarkSubscribeRSS Feed
rohitguptaecb
Calcite | Level 5

Hi All,

I have two data sets. Attached are them in xls format. Rainfall_data has rainfall value for each day for different years. Where as Cover_index has START_DATE and END_DATE for a particular phase. Now i want summation of rainfall for each year only for those dates that lies between START_DATE and END_DATE from Cover_index . I used the code as below:

PROC SQL;

CREATE TABLE TEST AS

SELECT YEAR(B.DATE),SUM(B.RAIN) FROM COVER_INDEX A, RAINFALL_DATA B

WHERE  (DAY(B.DATE) BETWEEN DAY(A.START_DATE) AND DAY(A.END_DATE))

AND (MONTH(B.DATE) BETWEEN MONTH(A.START_DATE) AND MONTH(A.END_DATE))

GROUP BY YEAR(B.DATE);

QUIT;

But its not giving me correct output. Pleae suggest how can we do this.

3 REPLIES 3
rohitguptaecb
Calcite | Level 5

There is one issue here . That is START_DATE and END_DATE has values 20Jun2010 and 09Jul2010. But i want summation of rainfall for dates that lies between 20jun and 09jul for all the years given in Rainfall_Data

rohitguptaecb
Calcite | Level 5

Guys help me out.

Haikuo
Onyx | Level 15

Looks like you have an emergency Smiley Happy,

PROC SQL;

CREATE TABLE TEST AS

SELECT YEAR(B.DATE),SUM(B.RAIN) FROM COVER_INDEX A, RAINFALL_DATA B

WHERE B.DATE BETWEEN mdy(month(A.START_DATE),DAY(A.start_DATE),year(b.date))

AND mdy(MONTH(A.end_DATE),day(a.end_date),year(b.date))

GROUP BY YEAR(B.DATE);

QUIT;

It may have typos, but you get the idea.

Haikuo

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
  • 3 replies
  • 642 views
  • 0 likes
  • 2 in conversation