BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jcabrera
Calcite | Level 5

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

6 REPLIES 6
Haikuo
Onyx | Level 15

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

Haikuo
Onyx | Level 15

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

jcabrera
Calcite | Level 5

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.

jcabrera
Calcite | Level 5

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.

Linlin
Lapis Lazuli | Level 10

try changing

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

to

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




Linlin

jcabrera
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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