BookmarkSubscribeRSS Feed
MarcBoh
Obsidian | Level 7

Hello there,

 

I want to collapse (group) my data by time (seconds). Sounds easy, well, but doesn't work.

I assume it is because CEIL does not round to integers but that's just a guess and I tried INT without success.

 

Why do I still get duplicate seconds observations? (The volume variable should be summed up when collapsing.)

So basically: I converted milliseconds to seconds and want to bring them to unique seconds observations. So, keep all observations, collapse seconds (which are duplicate) and sum up their values in the variable "volume". 

Any idea?

 

 

data temp;
set have;
seconds = ceil(int(miliseconds/1000));
run;

proc sql; 
  create table want as
  	select *, sum(volume) as SecVolume
  	from temp
		group by seconds;
quit;run;

proc sort data=want;
by id date seconds;
run;

 

4 REPLIES 4
Kurt_Bremser
Super User

Take a close look at the log of your SQL; you'll find a NOTE about remerging summary statistics.

If you include columns other than the summary values and the columns used in the group by, all observations will appear in the output, and the summary results will be merged back into all of them.

Your SQL should look rather like this:

proc sql; 
  create table want as
  	select id, date, seconds, sum(volume) as SecVolume
  	from temp
		group by id, date, seconds;
quit;run;

 

MaBo1011
Calcite | Level 5

Works perfect, thanks again Kurt 🙂

ballardw
Super User

I obviously don't know where you are going with this but I would suggest creating a SAS time valued variable. Then you can use a FORMAT that only displays to whole seconds (if I understand what your attempt at rounding is doing) for most procedures.

 


data example;
   input time;
datalines;
1.1
1.2
0.003
5.02
5.04
120.23
120.45
;
run;

proc freq data=example;
   tables time;
   format time time8.;
run;
MaBo1011
Calcite | Level 5

Thank you, solved it with proc sql but freq will also work!

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
  • 4 replies
  • 985 views
  • 0 likes
  • 4 in conversation