topic Re: Creating Subset of Samples in SAS Programming

Creating Subset of Samples

Zakir — Tue, 28 Aug 2018 07:22:21 GMT

Hi,

I have a stock trading data set which contains 'time', 'bidder', 'asker', 'price', and 'volume'. I want to create sub-samples based on each bidder (If there are 20 bidders, my sub samples will be 20 i. e., sub_sample1, sub-smaple2 and so on..). I want to keep those observations where bidder1 is also an asker somewhere in the asking column, bidder2 is also an asker somewhere in the asking column, and so on. I can do it by putting each bidder's name manually in the programming code but that is not efficient in the sense that if there were thousands of bidder, it would take a lot of time. So I need macro to do it. The files that I have and what I want look like the following. Please note that I am using SAS 9.4. Any help would be highly appreciated.

Thanks

Zakir

Data I haveSub Sample2 I wantSub sample1 I want

Re: Creating Subset of Samples

ChrisNZ — Tue, 28 Aug 2018 05:11:50 GMT

Unsure I understand your need.

I want to keep those observations where bidder is also an asker.

I don't see that in your samples.

Would this do?

proc sql;
  select t1.* 
  from HAVE t1
    , (select unique ASKER from HAVE) t2
  where t1.BIDDER =t2.ASKER;
quit;

Re: Creating Subset of Samples

Zakir — Tue, 28 Aug 2018 07:29:26 GMT

Hi Chris,

Thank you for your reply. Unfortunately, it did not work. I have edited the post to make it more clear. Would expect further help.

Kind regards,

Zakir

Re: Creating Subset of Samples

ballardw — Tue, 28 Aug 2018 15:20:27 GMT

@Zakir wrote:

Hi Chris,

Thank you for your reply. Unfortunately, it did not work. I have edited the post to make it more clear. Would expect further help.

Kind regards,

Zakir

Doesn't work is awful vague.

Are there errors in the log?: Post the code and log in a code box opened with the {i} to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

If you are going to provide example data, make sure that 1) your output can be made from the input data 2) Provide the data as a data step. I am not going to type your picture of data to create a data set that code can be tested with.

Your example "want" data shows only ByronHal as bidder and does not show any example with that as "Asker". So from the demonstrated data there is no way to fulfill your state requirement of " I want to keep those observations where bidder1 is also an asker somewhere in the asking column". So either your requirement is poorly stated or your example data is incomplete in regards to this requirement.

Re: Creating Subset of Samples

ChrisNZ — Tue, 28 Aug 2018 22:05:28 GMT

I still don't understand your needs for all the reasons @ballardw took a lot more care to explain than I would have.

I suggest that you provide a cut sample with few records, where only 2 output tables are created, and that you detail the logic that you are trying to implement.

As it is it is unclear.

Would expect further efforts making your requirements more obvious.