Transform multiple observations to a single observation

[aakash93]

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

[novinosrin · Accepted solution]

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;

[aakash93]

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.

[novinosrin]

Please provide your modified sample

[aakash93]

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

[aakash93]

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

[novinosrin]

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

[novinosrin]

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

[ballardw]

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.

[aakash93]

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

[ballardw]

---

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