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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.