topic Re: long to wide using seq by ID in SAS Programming

long to wide using seq by ID

Ronein — Thu, 05 Mar 2020 08:39:48 GMT

Hello

I have raw data of loans.

For each loan there are multiple rows that shows information about scores in following up periods .

I want to change the structure of the table from long to wide.

For each Loan_ID (Customer_ID,Loan_ID,Loan_date,Loan_Month,Loan_sum,Score_Known_Month) there will be only one row and there will be information of Score1,Score2,sscore3....Score11 (which is from fields :score +Seq)

What is the way to do it with proc transpose?

What is the way to do it with array?

what is better way?

 Data Rawtbl;
format Loan_date date9.;
informat Loan_date date9.;
input Customer_ID  Loan_ID Loan_date Loan_Month Loan_sum  Score_Known_Month score serial_no;
cards;
1 777 '23NOV2019'd 1911 3000 1910 7 1
1 777 '23NOV2019'd 1911 3000 1910 7 2
1 999 '17OCT2019'd 1910 1000 1908 7 1
1 999 '17OCT2019'd 1910 1000 1908 9 2
1 999 '17OCT2019'd 1910 1000 1908 7 3
1 999 '17OCT2019'd 1910 1000 1908 7 4
2 888 '27FEB2019'd 1902 900 1901 5 1
2 888 '27FEB2019'd 1902 900 1901 4 2
2 888 '27FEB2019'd 1902 900 1901 5 3
2 888 '27FEB2019'd 1902 900 1901 5 4
2 888 '27FEB2019'd 1902 900 1901 4 5
2 888 '27FEB2019'd 1902 900 1901 3 6
2 888 '27FEB2019'd 1902 900 1901 5 7
2 888 '27FEB2019'd 1902 900 1901 4 8
2 888 '27FEB2019'd 1902 900 1901 4 9
2 888 '27FEB2019'd 1902 900 1901 4 10
2 888 '27FEB2019'd 1902 900 1901 2 11
;
run;

/*Task:*/
/*For each: Customer_ID,Loan_ID,Loan_date,Loan_Month,Loan_sum,Score_Known_Month*/
/*Will have information of:*/
/*Score1 */
/*Score2*/
/*Score3*/
/*.......*/
/*.......*/
/*Score11*/

Re: long to wide using seq by ID

ed_sas_member — Thu, 05 Mar 2020 08:52:40 GMT

Hi @Ronein

Here are 2 methods:

proc transpose data=rawtbl out=rawtbl_tr (drop=_name_) prefix=score_;
	var score;
	by Customer_ID  Loan_ID Loan_date Loan_Month Loan_sum  Score_Known_Month;
run;

/* OR */
proc sql noprint;
	select max(nb_score) into:max_nb from 
	(select count(score) as nb_score from rawtbl
	group by Customer_ID, Loan_ID, Loan_date, Loan_Month, Loan_sum, Score_Known_Month);
quit;

data rawtbl_array;
	set rawtbl;
	array score_(&max_nb.);
 	by Customer_ID  Loan_ID Loan_date Loan_Month Loan_sum  Score_Known_Month;
    retain score;
 	if first.Score_Known_Month then do;
 		i=0;
 		call missing (of score_(*));
 	end;
 	retain score_;
 	i+1;
	score_(i) = score;
	if last.Score_Known_Month then output;
	drop score i serial_no;
run;

Re: long to wide using seq by ID

Ronein — Thu, 05 Mar 2020 09:32:42 GMT

Thank you.

I want to ask please about proc transpose:

Why don't we need to use ID statement in this case?

Is it equivalent way if I add ID statement with serial_no field?

proc transpose data=rawtbl out=rawtbl_tr (drop=_name_) prefix=score_;
var score;
by Customer_ID  Loan_ID Loan_date Loan_Month Loan_sum  Score_Known_Month;
ID serial_no;
run;

Re: long to wide using seq by ID

ed_sas_member — Thu, 05 Mar 2020 09:37:32 GMT

Hi @Ronein

Yes, it will be exactly the same results, as serial numbers are incremental (1, 2, etc.).

Best,