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 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

Multiple Linear Regression in SAS

Learn how to run multiple linear regression models with and without interactions, presented by SAS user Alex Chaplin.

Find more tutorials on the SAS Users YouTube channel.

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