BookmarkSubscribeRSS Feed
UshaLatha
Obsidian | Level 7

Hi all,

 

I am using PROC EXPAND to convert the monthly data into quarterly data. This has successfully converted the numeric variables.

But the character fields are dropped/cannot be converted using proc expand.I have two questions regarding the character variables.

 

1. I have a 'status' field which is character format, but changes over every month. I need the last row.

How can this be converted into quarterly?

 

2. They are few character fields which do not change i.e constant over time. In such cases, I need to retain the last row.

How can this be achieved?

 

My sample input data before using proc expand is:

loan_id period status worst_ever balance

1234  20010101  C  30 50000

1234  20010201  C  30 50000

1234  20010301  3  60 50000

1234  20010401  6  90 50000

1234  20010501  C  90 50000

1234  20010601  C  90 50000

 

Here for every quarter, I want to retain last value of worst_ever field, and last value of status .

output:

loan_id period status worst_ever balance

1234 2001:1 3 60 50000

1234 2001:2 C 90 50000

 

where (2001:1) is the quarter.

 

Please help me with this.

Thanks

 

 

 

3 REPLIES 3
Reeza
Super User

You can use LAST. processing with BY groups. Though if you're forced to go into a data step you may as well do all your conversions in the single data step to make it faster.

UshaLatha
Obsidian | Level 7

Hi,

 

I tried last.obs , but it gives three rows per quarter as shown below. Where balance is result of proc expand aggregate.

 

loan_id period status worst_ever balance

1234 2001:1 3 60 50000

1234 2001:1 3 60 .

1234 2001:1 3 60 .

1234 2001:2 C 90 50000

1234 2001:2 C 90 .

1234 2001:2 C 90 .

 

my desired output is:

loan_id period status worst_ever balance

1234 2001:1 3 60 50000

1234 2001:2 C 90 50000

 

last.obs doesn't work.

Puwang
Obsidian | Level 7

PROC EXPAND does not handle character variables. You can use data step to achieve the conversion. See the sample code below. The table quarter_data gives your desired result.


data indata;
	set indata;
	quarter = qtr(period);
	year = year(period);
run;
proc sort data = indata;
	by loan_id year quarter;
data quarter_data;
   	set indata;
	by loan_id year quarter;
	if last.quarter;
	drop year quarter;
run;

sas-innovate-white.png

Missed SAS Innovate in Orlando?

Catch the best of SAS Innovate 2025 — anytime, anywhere. Stream powerful keynotes, real-world demos, and game-changing insights from the world’s leading data and AI minds.

 

Register now

Discussion stats
  • 3 replies
  • 1348 views
  • 0 likes
  • 3 in conversation