DATA Step, Macro, Functions and more

Cell referencing

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Cell referencing

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

Currently, my data looks like this:

Trader Name
Trader Type
A1
B2
C3
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
A1
B2
C3
C3
B2
B2
C3
A1
B2

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.


Accepted Solutions
Solution
‎06-06-2012 08:13 PM
Respected Advisor
Posts: 3,156

Re: Cell referencing

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    .

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

View solution in original post


All Replies
Solution
‎06-06-2012 08:13 PM
Respected Advisor
Posts: 3,156

Re: Cell referencing

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    .

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

Respected Advisor
Posts: 3,156

Re: Cell referencing

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

Occasional Contributor
Posts: 10

Re: Cell referencing

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.

Occasional Contributor
Posts: 10

Re: Cell referencing

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.

Super Contributor
Posts: 1,636

Re: Cell referencing

try changing

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

to

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




Linlin

Occasional Contributor
Posts: 10

Re: Cell referencing

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 545 views
  • 9 likes
  • 3 in conversation