How to convert and retain character values after using PROC EXPAND

[UshaLatha]

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

[Reeza]

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]

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]

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;