About mrzlatan91

mrzlatan91 · ‎06-10-2018

Hey mate, first, I want to thank you so much for your solution. Sorry that I forgot to post my wanted dataset, but I guess this is exactly what i wanted (if column mode contains the mode of the values) Unfortunately, as I said, I have a lot of missing values, s.t. in column mode (and col1 as well) are only missing values, s.t. I can´t proof if the result is correct. Could you kindly tell me how to fix the problem with the missing values? Unfortunately, I´m an absolute SAS Noob. To understand it: table T represents the transposed table, _name_ is like a counting variable and col1 contains the values. Table T1 counts how often a specific value for a cusip-date combination exists. And Table Want contains all date-cusip combinations, count represents how often the mode value exists for that combination and mode represents the mode value, but what does column col1 then stand for? Have a nice day mate.

mrzlatan91 · ‎06-09-2018

Hey guys, I have a dataset like as follows: data work.class1; input date $ cusip_hld $ value1 $ value2 $ value3$ value4 $; datalines; 01DEC05 A 3 5 3 2 3 01DEC05 B 2 4 2 1 2 01JAN06 A 1 2 3 4 1 01JAN06 C 4 4 4 0 1 ; run; Is there an opportunity to add a column "classValue" which is calculated as the mode for each row, i.e. the mode for each date-cusip_hld-combination? (With mode I mean the value which appears most often, for example, the mode of 01JAN06 and Cusip_hld = C would be 4 in my table) I found some general solutions here, but I have 2 specific issues which makes it hard for me to reproduce those. First, it is possible (and often), that there are missing values in the value_i Columns. I only want to calculate the mode of the non-missing values in the row. Second, the code is in a macro. In each Iteration, one column value_i is added. The number of value_i columns is saved in the macro variable &fundNumber (if that helps). I´m really thankful of any suggestions or solutions. Kind regards, Zlatan

mrzlatan91 · ‎04-10-2018

Unfortunately, I can only test it on friday. I´ll tell you then if it works. But thanks mate 🙂

mrzlatan91 · ‎04-10-2018

Dear novinosrin, sorry, my mistake. I tried to make an other example before, but switched to the current one, but I may have copied the wrong table. Table b Looks like this: data b; input date $ customerId $ wealth $ Adress $; datalines; 01JAN01 1 100000 Stockholm 01JAN01 2 300000 Paris 01FEB01 1 120000 Munich 01MAR01 1 130000 New York 01MAR01 2 200000 Cologne 01MAR01 3 500000 London ; run;

mrzlatan91 · ‎04-10-2018

Thanks for your help. Ive tried this, but I´m not really sure if this is the right solution for my Problem. How can I define that I want a one-to-many relationship? data desired; if 0 then set work.b; /* loading in my small table b*/ declare hash VS(dataset:”work.b”); rc=VS.defineKey(“date”,”customerid”); /*key = date & customer id */ rc=VS.defineData(“date”,”customerid”, “wealth”); /*without address, cause I don’t want it*/ rc=VS.definedone(); do until(eof); set work.a end=eof; /*loading my big table */ call missing(date, customerid, revenue); /*Is This right? I don’t really get what call missing does to be honest */ rc=VS.find(); if rc=0 then output; /*because I want an inner join */ end; stop; run;

mrzlatan91 · ‎04-10-2018

Well,this is not really a course, It´s a part of my Master Thesis. I´ve tried to solve my Problem but unfortunately I´m not very familiar with Hash Tables, I´m only able to solve the Problem with SQL. So, If anyone else could help me I´d really appreciate it.

mrzlatan91 · ‎04-10-2018

Hi guys, I have 2 Tables with a One-To-Many Relationship. The first Table, lets say Table a, is a big Table which contains the columns Date, customerID and Revenue. The second Table b contains Date, customerID, wealth and adress. but only of a few (premium) rich customers. It´s possible, that we have more than one Observations with the same Date and Customer ID in Table 1 (If he buys more than one products in one month), in Table b, only one Observation for each Date/CustomerID-Combination is possible. So, the Tables could look like this: data a; input date $ customerId $ Revenue $ ; datalines; 01JAN01 1 10 01JAN01 1 20 01JAN01 2 30 01FEB01 1 400 01FEB01 2 50 01FEB01 3 60 01FEB01 4 30 01MAR01 1 60 01MAR01 2 30 01APR01 1 60 01APR01 2 60 01MAY01 1 800 01MAY01 2 608 01MAY01 2 67 01MAY01 4 65 01MAY01 5 644 ; run; And Table b could look like this: data b; input date $ customerId $ wealth $ Adress $; datalines; 01JAN01 1 1000 Stockholm 01JAN01 2 2000 Paris 01FEB01 1 4000 Munich 01MAR01 1 6000 New York 01MAR01 2 3000 Cologne 01MAR01 3 3000 London ; run; Now, I want to make an Inner Join to join both Tables when a.Date = b.Date and a.CustomerID = b.CustomerID. The final table should have the following columns: date, customerID, Revenue and wealth. In case that the criteria is met for more combinations of table a, the desired table should contain all rows. So in this Scenario, the final table should look like this: Date CustomerID Revenue Wealth Jan 01 1 10 100000 Jan 01 1 20 100000 Jan 01 2 30 300000 Feb 01 1 30 120000 Mrz 01 1 510 130000 Mrz 01 2 10 200000 Mrz 01 3 10 500000 This would not be a Problem with SQL, but my Professor wants that I use a Hash-Table, because for large datasets, this would be more efficient. Could someone kindly provide me a Hash Solution for this? Note: This is just a simplified example that I´ve made. Pls don´t care about the sense of the data, let´s say rich customers can Switch their adress and wealth every month or can be banned from the premium list in every month 🙂 With the kindest regards, Alex

mrzlatan91 · ‎04-07-2018

Thank you very much!

mrzlatan91 · ‎04-07-2018

Oh sorry, this was a mistake. Every Date/Value Combination of Table 2 is Unique. I think I should explain it a little bit. Table 1 contains Valuations (Value) for Stocks/Bonds (IDs), that are made by some Funds (Fundid) in a specific month (date). Table 2 contains Valuations for Stocks of ONE specific Fund, so every Date/Value Combination is unique.

mrzlatan91 · ‎04-07-2018

Oh **bleep**, I think this was the reason why I´ve made the distinct clause. What would be the solution if the distinct part is needed? Hash?

mrzlatan91 · ‎04-07-2018

Well I guess it´s not necessary. I coded this part 2 months ago and I don´t know why I did this to be honest.

mrzlatan91 · ‎04-07-2018

It depends on the Iteration, because I analyze every fund in my dataset. Some funds have 100,000 Observations, some funds only 100. Could you provide me a Hash-Solution?

mrzlatan91 · ‎04-07-2018

Sorry, I should have done this before. I´ve made a simplified version, because the size of my dataset is around 40GB. Both Tables have the same structure, but Table 1 is much bigger than Table 2. data table1; input date $ value $ id $ fundid $; datalines; 01OCT18 50 1234 10000 01OCT18 52 1234 10001 01OCT19 50 1234 10000 01OCT19 53 1234 10001 01OCT19 54 1234 10002 01NOV19 60 1236 10000 01NOV19 61 1236 10001 01NOV19 62 1236 10002 01OCT19 54 1234 10007 01NOV19 55 1236 10008 01NOV19 35 1236 10009 01NOV19 66 1236 10010 01DEC19 40 1236 10008 01DEC19 42 1236 10009 01DEC19 53 1236 10010 ; run; data table2; input date $ value $ id $ fundid $; datalines; 01OCT18 50 1234 10000 01OCT18 52 1234 10000 01OCT19 50 1234 10000 ; run; proc sql; create table work.result as select distinct a.*, b.value as newValue from work.table1 as a left join work.table2 as b on (a.id=b.id and a.date = b.date); quit;

mrzlatan91 · ‎04-06-2018

Wow, that sounds good. Table 1 contains a lot of observations, table 2 has always a part of it, lets say less than 1% (it depends on the iteration) Unfortunately, I don´t know how to use the Hash Function to solve this problem. Could anyone provide me a solution for my posted problem?

mrzlatan91 · ‎04-06-2018

Hey guys, Im a student from Germany (so sorry for my bad english) and Im having a Code with, containing a SQL step with Inner Join, but the program runs tooooo slowly. I guess there could be a faster version (e.g. with a data step) for the following code: proc sql; create table work.Result as select distinct a.*, b.Value from work.Table1 As a left join work.Table2 As B on (a.id = b.id and a.date = b.date); quit; The problem is that my program contains a macro and the code runs very often, so every second counts 🙂 I´d appreciate it so much if you could help me with that. Thanks in advance, Alex

Online Status	Offline
Date Last Visited	‎12-02-2018 07:35 AM

Re: Problems with a Join (Cartesian Product?)

Problems with a Join (Cartesian Product?)

Re: Combine 2 Tables

Re: Combine 2 Tables

Combine 2 Tables

Re: Performance Issues with merging (Hash Solution needed)

Re: Performance Issues with merging (Hash Solution needed)

Re: Performance Issues with merging (Hash Solution needed)

Re: Performance Issues with merging (Hash Solution needed)

Re: Performance Issues with merging (Hash Solution needed)

Re: Calculate mode for each row

Re: Performance Issues with merging (Hash Solution needed)

Re: Problems with Merging

Re: Calculate mode for each row

Calculate mode for each row

Re: Hash Table

Re: Hash Table

Re: Hash Table

Re: Hash Table

Hash Table

Re: Data Step instead of Left Join?

Re: Data Step instead of Left Join?

Re: Data Step instead of Left Join?

Re: Data Step instead of Left Join?

Re: Data Step instead of Left Join?

Re: Data Step instead of Left Join?

Re: Data Step instead of Left Join?

Data Step instead of Left Join?