BookmarkSubscribeRSS Feed
Atulya212
Fluorite | Level 6

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 Dateaccount no.DPD Account no.DPD Mar-18DPD Jun-18DPD Sep18DPD Dec-18DPD Mar-19DPD Jun-19
Mar-1810164 101647961763342
Jun-1810179        
Sep-1810161        
Dec-1810176        
Mar-1910133        
Jun-1910142        
2 REPLIES 2
ed_sas_member
Meteorite | Level 14

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
Super User

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;

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

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
  • 2 replies
  • 337 views
  • 0 likes
  • 3 in conversation