topic Re: merging data in a dataset in SAS Programming

merging data in a dataset

Joostvanerp — Fri, 31 Jul 2020 11:51:12 GMT

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?

Re: merging data in a dataset

PaigeMiller — Fri, 31 Jul 2020 13:07:53 GMT

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/

Re: merging data in a dataset

Joostvanerp — Fri, 31 Jul 2020 14:19:19 GMT

This are the tables in pdf

Re: merging data in a dataset

ballardw — Fri, 31 Jul 2020 15:56:59 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: merging data in a dataset

PaigeMiller — Fri, 31 Jul 2020 16:04:05 GMT

@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.

Re: merging data in a dataset

hhinohar — Sat, 01 Aug 2020 07:20:36 GMT

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=p0t2ac0tfzcgbjn112mu96hkgg9o.htm&locale=ja#p0ooiohfq20an9n1qrt7ddev0rql