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

Untitled picture.png

I am trying to sum units based on the start_dt where concent_cnt is 1 to 5 

i need a sum of units based on range of dates 

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star

Basically you need GROUP BY, not ORDER BY:

 

proc sql;
  select np_resource_id,start_dt,activity_dt as end_dt,
         sum(unit) as total_units,
         max(consec_cnt) as final_consec,
	 end_dt-start_dt as difference
  from have
  group by np_resource_id,start_dt
  having consec_cnt=final_consec and final_consec >=5;
quit;

 

This will produce one record per np_resource_id/start_dt combination, but just those with a maximum consec_cnt>=5.

 

The program assumes for there are no "holes" in consec_cnt.  I.e. if there is a consec_cnt=6, there must also be a 1,2,3,4, and 5.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

3 REPLIES 3
ballardw
Super User

Your dates look like they are character variables. If so you likely want to create new variable that are SAS date valued numeric to use "range" of dates.

What specific range of dates do you need? You don't mention it. Also you picture of the data does not include a variable named concent_cnt  so saying anything about that variable isn't much help.

hk2013
Fluorite | Level 6

its actually consec_cnt (which is counting 5 consecutive days) 

 

i do have a range of five days but when i sum the units it sums all the units but i want sum of units for specific dates 

 

PROC SQL ;
CREATE TABLE NEW AS
SELECT NP_RESOURCE_ID, category, START_DT, ACTDATE As End_DT, CONSEC_CNT,
INTCK('day',START_DT, ACTDATE) as DAY_DIFFERENCE
FROM cosick1
WHERE CONSEC_CNT >= 5
ORDER BY NP_RESOURCE_ID, ACTDATE ;
QUITUntitled picture.png

mkeintz
PROC Star

Basically you need GROUP BY, not ORDER BY:

 

proc sql;
  select np_resource_id,start_dt,activity_dt as end_dt,
         sum(unit) as total_units,
         max(consec_cnt) as final_consec,
	 end_dt-start_dt as difference
  from have
  group by np_resource_id,start_dt
  having consec_cnt=final_consec and final_consec >=5;
quit;

 

This will produce one record per np_resource_id/start_dt combination, but just those with a maximum consec_cnt>=5.

 

The program assumes for there are no "holes" in consec_cnt.  I.e. if there is a consec_cnt=6, there must also be a 1,2,3,4, and 5.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

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
  • 1004 views
  • 0 likes
  • 3 in conversation