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 |
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;
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;
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.
Please provide your modified sample
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 | |||
Also forgot to mention this is after I converted INCM ASTS and LIAB to numeric.
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
ARBRT | 2015Q1 | 130,967 | 2,742,733 | 1,892,937 | Y |
JPMOR | 2015Q2 | 100,267 |
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
ARBRT | 2015Q1 | 130967 | 2742733 | 1892937 | Y |
JPMOR | 2015Q2 | 100267 | . | . |
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.
This solution works as well. What if I add an additional variable such as Y/N indicator that is character type?
@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.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.