topic Re: Rearranging the Data Structure in SAS Programming

Rearranging the Data Structure

Atulya212 — Fri, 22 May 2020 09:56:28 GMT

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

Re: Rearranging the Data Structure

ed_sas_member — Fri, 22 May 2020 10:00:49 GMT

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,

Re: Rearranging the Data Structure

Kurt_Bremser — Fri, 22 May 2020 10:57:38 GMT

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;