topic How to convert and retain character values after using PROC EXPAND in SAS Forecasting and Econometrics

How to convert and retain character values after using PROC EXPAND

UshaLatha — Tue, 28 Nov 2017 17:56:07 GMT

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

Re: How to convert and retain character values after using PROC EXPAND

Reeza — Tue, 28 Nov 2017 18:00:00 GMT

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.

Re: How to convert and retain character values after using PROC EXPAND

UshaLatha — Tue, 28 Nov 2017 18:41:04 GMT

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:2 C 90 50000

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.

Re: How to convert and retain character values after using PROC EXPAND

Puwang — Fri, 15 Dec 2017 19:23:25 GMT

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;