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.
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
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
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
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.
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.
try changing
dcl hash h(dataset:'&dsLib..cohort1_03_copy');
to
dcl hash h(dataset:"&dsLib..cohort1_03_copy");
Linlin
Thank you guys very much. Even though I will have to spend several hours figuring out how these commands work, they did work.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: