topic Re: Custom Sorting in SAS Programming

Custom Sorting

Pabster — Wed, 06 Mar 2019 18:23:05 GMT

Hi ,

I have two data sets derived from the same original set:

A) Which is where I have managed to sort companies by their respective SIC CODE and GVKEY with a variable horizon input. The Variable give me the opportunity to change how many years prior bankruptcy(or the last data available for non bankrupt firms) I want to see.

B) Another data set I created. This one only contains the last input of each company. It is sorted by year and sector. The way it sorted let me make comparisons between a bankrupt firm (costat=1) and a nonbankrupt one (Costaty=0) from the same industry at a precise year.
That`s the way I want to see it BUT with the horizon variable that let s me check year prior the bankruptcy

My question is :

IS there any way I can tell sas to keep the same order from the b) dataset and put that order in the A) data set. I would probably have to be with the GVKEYS I suppose

Re: Custom Sorting

noling — Wed, 06 Mar 2019 18:31:53 GMT

Your comment

That`s the way I want to see it BUT with the horizon variable that let s me check year prior the bankruptcy

makes me think you should just pull the horizon variable from your A) table into your B) table. Is that possible for you using proc sql or another join?

Re: Custom Sorting

Pabster — Wed, 06 Mar 2019 18:47:18 GMT

*UPDATE*

I Took out the order from the B) data set with this code:

data Order;
set B);
order=_N_;
keep gvkey order;
run;

but now when I merge it with the A data set the order from the A is unchanged...

Re: Custom Sorting

Pabster — Wed, 06 Mar 2019 18:41:11 GMT

Yeah that`s pretty much how I ended up with the A data set but the order is messed up now

Re: Custom Sorting

noling — Wed, 06 Mar 2019 18:51:43 GMT

You could:

1. Use a data step and SET table B. Load table A into a hash, and pull in the desired field.

2. (looks like you're trying this) Create a new var on Table B with is = _N_, then proc sql and LEFT JOIN table A to table B (table B on left), then sort the new table by your new _N_ variable

3. A data step merge would probably also work if you can sort both tables, and have table A only have your key and desired field

Do those options make sense?

Re: Custom Sorting

Pabster — Wed, 06 Mar 2019 19:14:59 GMT

I have no clue on how to create and use a hash sadly.

I will try the proc sql merge and let you know

Re: Custom Sorting

Pabster — Wed, 06 Mar 2019 20:16:56 GMT

@noling. cant manage to make it work

Re: Custom Sorting

noling — Thu, 07 Mar 2019 13:55:01 GMT

Does this approach make sense? Let's say that in this case we want to pull MAKE from table A into table B, and maintain table B's order. The key in this case is MODEL:

data A ;
	set sashelp.cars(obs=10);
	keep make model;
run;
data B;
	set sashelp.cars(obs=10);
	keep msrp model ob_no;
	ob_no=_N_;
run;

*want to pull the make value from table A into table B, and maintain table B's order;
proc sql noprint;
	create table want(drop=ob_no) as
	select B.*, make
	from B left join
		A on B.model =A.model
	order by ob_no;
quit;

Re: Custom Sorting

andreas_lds — Thu, 07 Mar 2019 14:46:10 GMT

@Pabster wrote:

Yeah that`s pretty much how I ended up with the A data set but the order is messed up now

The order of variables is hardly relevant for anything, except proc export.

Re: Custom Sorting

Pabster — Thu, 07 Mar 2019 15:12:43 GMT

My supervisor has asked me to give it to him in a specific way. and it is kind of relevant for the research

Re: Custom Sorting

Pabster — Thu, 07 Mar 2019 15:13:23 GMT

I got 2000 less observation on my A dataset now but it indeed works !