topic Re: Cell referencing in SAS Programming

Cell referencing

jcabrera — Wed, 06 Jun 2012 23:22:02 GMT

I'm having a hard time figuring the code for the following change:

Currently, my data looks like this:

Trader Name	Trader Type
A	1
B	2
C	3
C	.
B	.
B	.
C	.
A	.
B	.

So the trader type (1, 2 or 3) is "linked" to the correct trader name (A, B or C) only on the first 3 rows. I would like to have my dataset look like tis:

Trader Name	Trader Type
A	1
B	2
C	3
C	3
B	2
B	2
C	3
A	1
B	2

In other word I want to write the correct trader type next to the trader name for all the rows. Of course my dataset is much bigger but I can figure out the steps.

Any help would be appreciated.

Thank you.

Re: Cell referencing

Haikuo — Thu, 07 Jun 2012 00:13:09 GMT

well, if you don't care about the original order, it is simple, you can use either data step (proc sort + set) or SQL, here is an SQL example:

data have;

input (Trader_Name Trader_Type) ($);

cards;

A 1

B 2

C 3

C .

B .

C .

A .

B .

;

proc sql;

select a.trader_name,COALESCEC(a.trader_type, b.trader_type)

from have a

left join have (where=(not missing(trader_type))) b

on a.trader_name=b.trader_name

;

quit;

However, if you have to maintain the order, it will need more mileage. You could try either proc format to look up or hash(). Here is an example using hash():

	data want;
	if _n_=1 then do;
	set have (obs=1);
	dcl hash h(dataset:'have');
	h.definekey('trader_name');
	h.definedata('trader_type');
	h.definedone();
	end;

	set have;
	rc=h.find();
	drop rc;

run;

Haikuo

Re: Cell referencing

Haikuo — Thu, 07 Jun 2012 00:36:49 GMT

FWIW, here is the proc format approach:

data fmt;

retain fmtname '$trader';

set have (rename=(trader_name=start trader_type=label) where=(not missing(label)));

run;

proc format library=work cntlin=fmt;

run;

data want;

set have (drop=trader_type);

trader_type=put(trader_name, $trader.);

run;

Haikuo

Re: Cell referencing

jcabrera — Thu, 07 Jun 2012 01:07:23 GMT

I too the data step code with the hash commands and adapted them to my dataset and variable names so it looks like this:

data &dsLib..cohort1_04_copy;

if _n_=1 then do;

set &dsLib..cohort1_03_copy (obs=1);

dcl hash h(dataset:'&dsLib..cohort1_03_copy');

h.definekey('Trader1');

h.definedata('tType');

h.definedone();

end;

set &dsLib..cohort1_03_copy;

rc=h.find();

drop rc;

run;

However, I'm getting the following error on the log (line 202 is the line that contains "h.definedone();")

ERROR: Invalid data set name at line 202 column 9.

ERROR: Hash data set load failed at line 202 column 9.

ERROR: DATA STEP Component Object failure. Aborted during the EXECUTION phase.

I dont really undesrtand how the has command works so I'm having trouble figuring the error out. Your code works, I'm not sure why mine doesnt. My actual dataset contains other variables, could that be the reason?

Thank you.

Re: Cell referencing

jcabrera — Thu, 07 Jun 2012 01:22:28 GMT

My dataset name is: &dsLib..cohort1_03_copy

And my actual variable names are: Trader1 and tType

I tried the proc format method and the first step (datastep works) but the other two steps (proc format and final data step) give me errors. It looks like the $trader. format is not being created in the work library. This is all very advanced for me right now so I cant figure it out but i will continue staring at the code.

Thank you.

Re: Cell referencing

Linlin — Thu, 07 Jun 2012 01:25:10 GMT

try changing

dcl hash h(dataset:'&dsLib..cohort1_03_copy');

dcl hash h(dataset:"&dsLib..cohort1_03_copy");

Linlin

Re: Cell referencing

jcabrera — Thu, 07 Jun 2012 12:11:07 GMT

Thank you guys very much. Even though I will have to spend several hours figuring out how these commands work, they did work.