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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.