topic Re: Transform multiple observations to a single observation in SAS Procedures

Transform multiple observations to a single observation

aakash93 — Fri, 20 Jul 2018 16:20:41 GMT

Hi,

I'm looking for a solution to transform multiple observations into a single observation. I've included the current state and future state images below. (Note: all values are in character format)

LNDR_FMLY_ID	QTR_ID	INCM	ASTS	LIAB
ARBRT	2015Q1	130,967
ARBRT	2015Q1		2,742,733
ARBRT	2015Q1			1,892,937
JPMOR	2015Q2	100,267

LNDR_FMLY_ID	QTR_ID	INCM	ASTS	LIAB
ARBRT	2015Q1	130,967	2,742,733	1,892,937
JPMOR	2015Q2	100,267

Re: Transform multiple observations to a single observation

novinosrin — Fri, 20 Jul 2018 16:26:49 GMT

data have;
input (LNDR_FMLY_ID	QTR_ID	INCM	ASTS	LIAB) (:$25.);
cards;
ARBRT	2015Q1	130,967	 	. .
ARBRT	2015Q1	 .	2,742,733	. 
ARBRT	2015Q1	 	. . 	1,892,937
JPMOR	2015Q2	100,267	 . .	 
;
data want;
update have(obs=0) have;
by LNDR_FMLY_ID QTR_ID;
run;

Re: Transform multiple observations to a single observation

ballardw — Fri, 20 Jul 2018 16:25:50 GMT

If the variables INCM ASTS and LIAB are numeric and there are no duplicates for combinations of LNDR_FMLY_ID and QTR_ID then

Proc summary data=have nway;

class LNDR_FMLY_ID QTR_ID ;

var INCM ASTS LIAB ;

output out=want (drop= _type_ _freq_) max=;

run;

would be one way.

Re: Transform multiple observations to a single observation

aakash93 — Fri, 20 Jul 2018 17:33:42 GMT

This solution works as well. What if I add an additional variable such as Y/N indicator that is character type?

Re: Transform multiple observations to a single observation

aakash93 — Fri, 20 Jul 2018 17:56:06 GMT

Similar to my question to Ballardw, what if I add an additional variable such as Y/N indicator variable that is character type? Adding a character type variable results in a null value.

Re: Transform multiple observations to a single observation

novinosrin — Fri, 20 Jul 2018 17:57:13 GMT

Please provide your modified sample

Re: Transform multiple observations to a single observation

aakash93 — Fri, 20 Jul 2018 18:03:21 GMT

LNDR_FMLY_ID	QTR_ID	INCM	ASTS	LIAB	BANK_IND (character)
ARBRT	2015Q1				Y
ARBRT	2015Q1	130,967
ARBRT	2015Q1		2,742,733
ARBRT	2015Q1			1,892,937
JPMOR	2015Q2	100,267




LNDR_FMLY_ID	QTR_ID	INCM	ASTS	LIAB	BANK_IND (character)
ARBRT	2015Q1	130,967	2,742,733	1,892,937	Y
JPMOR	2015Q2	100,267

Re: Transform multiple observations to a single observation

aakash93 — Fri, 20 Jul 2018 18:05:33 GMT

Also forgot to mention this is after I converted INCM ASTS and LIAB to numeric.

Re: Transform multiple observations to a single observation

ballardw — Fri, 20 Jul 2018 18:06:12 GMT

@aakash93 wrote:

This solution works as well. What if I add an additional variable such as Y/N indicator that is character type?

Example input and output. Depends on if the indicator changes between your original records for the same group or not. If it does a rule for what the final should be.

Re: Transform multiple observations to a single observation

novinosrin — Fri, 20 Jul 2018 18:08:31 GMT

Here is my test and results:

data have;
infile cards truncover;
input (LNDR_FMLY_ID	QTR_ID	INCM	ASTS	LIAB	BANK_IND) (:$25.);
cards;
ARBRT	2015Q1	. .  .	 	 	Y
ARBRT	2015Q1	130,967	 . . .	 	 
ARBRT	2015Q1	 .	2,742,733	. . 	 
ARBRT	2015Q1	 .  .	 	1,892,937	. 
JPMOR	2015Q2	100,267	 .  .  .	 	 
;

data want;
update have(obs=0) have;
by LNDR_FMLY_ID QTR_ID;
run;
proc print noobs;
run;

SAS Output

LNDR_FMLY_ID QTR_ID INCM ASTS LIAB BANK_IND

ARBRT	2015Q1	130,967	2,742,733	1,892,937	Y
JPMOR	2015Q2	100,267

Re: Transform multiple observations to a single observation

novinosrin — Fri, 20 Jul 2018 18:11:53 GMT

After reading your conversion message, yet another test:

data have;
infile cards truncover;
input LNDR_FMLY_ID :$20.	QTR_ID :$20.	INCM	ASTS	LIAB	BANK_IND :$2.;
cards;
ARBRT	2015Q1	. .  .	 	 	Y
ARBRT	2015Q1	130967	 . . .	 	 
ARBRT	2015Q1	 .	2742733	. . 	 
ARBRT	2015Q1	 .  .	 	1892937	. 
JPMOR	2015Q2	100267	 .  .  .	 	 
;

data want;
update have(obs=0) have;
by LNDR_FMLY_ID QTR_ID;
run;
proc print noobs;
run;

SAS Output

LNDR_FMLY_ID QTR_ID INCM ASTS LIAB BANK_IND

ARBRT	2015Q1	130967	2742733	1892937	Y
JPMOR	2015Q2	100267	.	.