BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

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*/
3 REPLIES 3
ed_sas_member
Meteorite | Level 14

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;
Ronein
Meteorite | Level 14

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;
ed_sas_member
Meteorite | Level 14

Hi @Ronein 

 

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

 

Best,

 

 

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 365 views
  • 0 likes
  • 2 in conversation