merging data in a dataset

[Joostvanerp]

Hi everbody,

I would like to transform my dataset for further analysis, as in the document attached.

The goal is to get the data together with the same selectionnumber. The cownumber and the datetime needs do stay the same, but the seconds need to be summated. 

I can't get this working. 

Which code / sas function is needed for this?

[PaigeMiller]

Most people will not download and open Excel files. It is a security threat. Please provide data according to these instructions: https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/

[Joostvanerp]

This are the tables in pdf 

[PaigeMiller]

---

@Joostvanerp wrote:

This are the tables in pdf 

---

In order to help you write code to solve your problem, the data needs to be in SAS data step code so we can work with the exact same data that you have.

[ballardw]

Best is to provide example data in the form of data step code. With a data step we can see at a glance the names and types of variables (common sources of confusion when looking at PICTURES) as well being able to run code. Only include variables actually needed for the problem at hand.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... 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 </> icon or attached as text to show exactly what you have and that we can test code against.

[hhinohar · Accepted solution]

Hello,

From what you have given to this thread,I assume following is expected.
1. Group by cownumber and selection number and summate seconds by each group.
2. However datetime should hold first observation of each group.

If this is your expectation, then SAS code below should do what is necessary.

/* input dataset */
data test(drop=datetm rename=(datetm1=datetm));
length cownum 8 datetm $ 16 seconds 8 selectionnm 8;
infile datalines dsd dlm="09"x;
input cownum  datetm $ seconds  selectionnm;
/* As datetime is in character,it needs to be traslated into datetime */
datetm1=input(datetm,datetime.);
format datetm1 nldatm48.;
cards;
11	13MAR20:09:10:41	10	1
11	13MAR20:09:10:55	25	1
11	13MAR20:09:11:41	10	1
11	13MAR20:09:12:41	35	2
14	13MAR20:09:10:41	10	1
14	13MAR20:09:12:41	25	2
14	13MAR20:09:13:41	24	3
14	13MAR20:09:14:41	35	3
;
run;

/* sort by cownum,selection */
proc sort data=work.test;
	by cownum selectionnm;
run;

option locale=en_US;
/* summate by cownum selection number */
data test2(drop=seconds datetm rename=(sumsec=seconds timegrp=datetm));
length cownum 8 datetm 8 seconds 8 selectionnm 8 timegrp $48;
	set work.test;
		by cownum selectionnm;
/* 	first retain sumsec variable and timegrp variable for grouping(seconds and datetime)*/
	retain sumsec 0;
	retain timegrp "";

/* set to 0 when obs hits first selection number group	 */
/* 	datetime should hold the first obs for each group */
	if first.selectionnm then do;
		sumsec=0;
		timegrp=put(datetm,nldatm48.);
	end;
/* 	summate seconds by cownum selection number group */
	sumsec=sumsec+seconds;
	
/* output at the end of each group */
	if last.selectionnm then do;
		output;
	end;
run;

Following SAS retain statement link should explain above sample.(Example3)

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.5&docsetId=lestmtsref&docsetTarget=p...