Hi Everyone,
I have a trading data set which contains time, bidder, asker, price and volume variable. The data set looks like this:
I want to create dummy variables based on unique bidder. The idea is if a bidder appears in the bidder column, dummy variable should take 0 and if the same bidder appears anywhere in the asking column, dummy variable should take 1. If write the code, it looks:
data want; set have;
if bidder="ByronHal" then dum1=0; if asker="ByronHal" then dum1=1;
if bidder="EdyssaPal" then dum2=0; if asker="EdyssaPal" then dum2=1;
...........
if bidder="ZacharyBra" then dum9=0; if asker="ZacharyBra" then dum9=1;
run;
The problem for this code is manual. I need to look manually who are bidders and type their names. Is there any way to do this by the program!! I mean let the program decide how many unique bidders are there and generate dummies. Finally, can I get sub samples for each bidder from that file. The final sub samples for each bidder should look like this:
Sub Sample for bidder1
sub sample for bidder2
Let me thank you in advance for helping!!
Zakir
You can do it something like this:
data have;
input bidder $ asker $;
cards;
a b
a c
b a
c d
;run;
proc sql;
create table brokers as select monotonic() as id,broker
from(select bidder as broker from have union select asker as broker from have);
%let n=&sqlobs;
create index broker on brokers(broker);
quit;
data dummy;
set have;
array dummies (*) dum1-dum&n;
set brokers(rename=(broker=asker)) key=asker/unique;
dummies(id)=1;
set brokers(rename=(broker=bidder)) key=bidder/unique;
dummies(id)=0;
drop id;
run;
But what are you goint to use that for? It is very bad programming practice to put data (broker IDs) into variable names.
I'd create formats from the names, set one new variable using that format, and then transpose to the wide format with the multiple dummies.
Please supply example data in a readily usable format (data step with datalines).
You can do it something like this:
data have;
input bidder $ asker $;
cards;
a b
a c
b a
c d
;run;
proc sql;
create table brokers as select monotonic() as id,broker
from(select bidder as broker from have union select asker as broker from have);
%let n=&sqlobs;
create index broker on brokers(broker);
quit;
data dummy;
set have;
array dummies (*) dum1-dum&n;
set brokers(rename=(broker=asker)) key=asker/unique;
dummies(id)=1;
set brokers(rename=(broker=bidder)) key=bidder/unique;
dummies(id)=0;
drop id;
run;
But what are you goint to use that for? It is very bad programming practice to put data (broker IDs) into variable names.
Hi s_lassen,
Thank you for your kind help. The first part of the problem is solved. Would you please advise me how to do sub-sampling based on each bidder which will contain observations as shown below:
May I request to provide macro codes so that I can get each bidder's trading detail in one run.
And the rule(s) for creating the subsample are what if any?
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 25. 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.