Custom Sorting

[Pabster]

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

[noling]

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?

[Pabster]

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

[andreas_lds]

---

@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.

[Pabster]

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

[Pabster]

*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...

[noling]

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?

[Pabster]

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

I will try the proc sql merge and let you know 

[Pabster]

@noling. cant manage to make it work

[noling · Accepted solution]

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;

[Pabster]

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