Rearranging the Data Structure

[Atulya212]

Hi All,

I have received a SAS dataset from the client which contains account level information at quarterly intervals. It's an appended table that contains information like account no.,  MIS date, Outstanding Balance, and Days Past Due. So how could I create a new table to get the quarter on quarter DPDs for each of the account numbers. My original dataset contains records from Mar'12 to Mar'20 and about 52 million records. Below I have provided the sample structure of the table which I intended to achieve.

Old Structure				New Structure
MIS Date	account no.	DPD		Account no.	DPD Mar-18	DPD Jun-18	DPD Sep18	DPD Dec-18	DPD Mar-19	DPD Jun-19
Mar-18	101	64		101	64	79	61	76	33	42
Jun-18	101	79
Sep-18	101	61
Dec-18	101	76
Mar-19	101	33
Jun-19	101	42

[ed_sas_member]

Hi @Atulya212 

You can run a PROC TRANSPOSE:

proc transpose data=have out=want (drop=_:) prefix=DPD_;
	var DPD;
	by account_no;
	id MIS_DATE;
run;

Best,

[Kurt_Bremser]

Why do you want to complicate your future work by hiding data (dates) in structure (column names), creating a dynamic data structure that forces you to write dynamic code unnecessarily, will create lots of unnecessarily wasted space because of missing values, and so on?

For reporting purposes, you can use the period dates as ACROSS variable in PROC REPORT:

data have;
input Date :monyy6. account_no $ DPD;
format date yymmd7.;
datalines;
Mar-18 101 64
Jun-18 101 79	 	 	 	 	 	 	 	 
Sep-18 101 61	 	 	 	 	 	 	 	 
Dec-18 101 76	 	 	 	 	 	 	 	 
Mar-19 101 33	 	 	 	 	 	 	 	 
Jun-19 101 42
;

proc report data=have;
column account_no dpd,date;
define account_no /group;
define date /across;
define dpd /analysis;
run;