About Lucas

Lucas · ‎09-07-2020

Briliant solution in easy way. Values datetimeTo are different than in my example but it's fine for me. Thank you very much @PGStats!

Lucas · ‎09-06-2020

Hi all, I have table with 3 columns (id;datetime;status) and I need to create new table with periods between two statuses: CLOSE-REOPEN. Have: id datetime status 1 06SEP2020:12:00:00 open 1 06SEP2020:12:00:01 CLOSE 1 06SEP2020:12:00:02 s1 1 06SEP2020:12:00:03 s2 1 06SEP2020:12:00:04 REOPEN 1 06SEP2020:12:00:05 s1 1 06SEP2020:12:00:06 CLOSE 1 06SEP2020:12:00:07 s1 1 06SEP2020:12:00:08 REOPEN 2 06SEP2020:12:00:00 open 2 06SEP2020:12:00:01 CLOSE 2 06SEP2020:12:00:02 s1 Want: id status1 status2 datetime_from datetime_to 1 CLOSE REOPEN 06SEP2020:12:00:01 06SEP2020:12:00:03 1 CLOSE REOPEN 06SEP2020:12:00:06 06SEP2020:12:00:07 Is it possible to achieve this with SQL Query? I can use only PROC SQL. It would be great to do this with one query but if it is not possible then more queries are also fine.

Lucas · ‎08-28-2015

Hi, thank you for quick response. I used your code but one combination is missing (A C)

Lucas · ‎08-28-2015

Hello, I have a list of variables: data tab; input var $; datalines; A B C ; I need to create unique combinations of these variables: A A B A B C A C B B C C Thank you in advance for your help.

Lucas · ‎01-26-2015

Thank you for all responses. I have read more about views and I think there is no way to achieve what I want. I will provide more details about my problem. I think now it will be easier to understand. There is a process that creates a table once a moth: INCOMES_201410 INCOMES_201411 INCOMES_201412 INCOMES_201501 INCOMES_201502 I need to create a view which provides data from all tables INCOMES_*, but I don't want to change definition of the view each month. I was hoping there is a way to create a view once and when someone use this view it automatically check which tables to use.

Lucas · ‎01-23-2015

Hi, I'm trying to create a view with parameterized list of tables. View definition must be create once and must be automatically refreshed. List of tables is keep in a data set which can be edit by the user. I was trying to create a macro in autocall library and use it in view definition. Like this: %macro table; sashelp.class sashelp.class %mend; data v_table / view=v_table; set %table; run; but I don't know how to create table list based on data set: data tables; input table_nm $32.; datalines; sashelp.class sashelp.class ; %macro table; <query to the 'tables' data set > %mend; data v_table / view=v_table; set %table; run; Maybe there is an easier way ?

Lucas · ‎07-21-2014

@Tom, It was clever It seems that it is ok. Thanks

Lucas · ‎07-21-2014

Hi, I like this solution because it's clear but I see potential performance problems. With 500k customers and an average of 3 magazines we have to merge 1.5M tables. I've never merged so many tables but I am affraid it will be a problem even if the tables are small. I will try to make some tests.

Lucas · ‎07-18-2014

I need outputed periods per day so DATA STEP with select(month); when("1") ...... isn't good for this problem. Of course I can write as many 'select when' as days but i'm affraid that there will be huge problems with performance. For example: data subscriptions; infile datalines; input @01 id 1. @03 magazine $4. @08 date_from date9. @18 date_to date9.; datalines; 1 mag1 01jan2014 15mar2014 1 mag2 20feb2014 12apr2014 1 mag3 20feb2014 12apr2014 1 mag4 25mar2014 16may2014 2 mag1 01jan2014 01jul2014 2 mag2 01feb2014 01apr2014 Result should be: ID magazine date_from date_to --------------------------------------- 1 mag1 01jan2014 20feb2014 1 mag1 20feb2014 15mar2014 1 mag2 20feb2014 15mar2014 1 mag3 20feb2014 15mar2014 1 mag2 15mar2014 25mar2014 1 mag3 15mar2014 25mar2014 1 mag2 25mar2014 12apr2014 1 mag3 25mar2014 12apr2014 1 mag4 25mar2014 12apr2014 1 mag4 12apr2014 16may2014 2 mag1 01jan2014 01feb2014 2 mag1 01feb2014 01apr2014 2 mag2 01feb2014 01apr2014 2 mag1 01apr2014 01jul2014

Lucas · ‎07-18-2014

Thanks for reply. Unfortunately, the task is more complicated because the dates valid_from/valid_to do not have to be the first day of the month. It can be any day 😕

Lucas · ‎07-18-2014

Problem: the client has a subscription of various magazines. I need to find a common periods of subscriptions. Example: data subscriptions; infile datalines; input @01 id 1. @03 magazine $4. @08 date_from date9. @18 date_to date9.; datalines; 1 mag1 01jan2014 01mar2014 1 mag2 01feb2014 01apr2014 1 mag3 01feb2014 01apr2014 1 mag4 01mar2014 01may2014 2 mag1 01jan2014 01jul2014 2 mag2 01feb2014 01apr2014 ; I need to get the result: ID magazine date_from date_to --------------------------------------- 1 mag1 01jan2014 01feb2014 1 mag1 01feb2014 01mar2014 1 mag2 01feb2014 01mar2014 1 mag3 01feb2014 01mar2014 1 mag2 01mar2014 01apr2014 1 mag3 01mar2014 01apr2014 1 mag4 01mar2014 01apr2014 1 mag4 01apr2014 01may2014 2 mag1 01jan2014 01feb2014 2 mag1 01feb2014 01apr2014 2 mag2 01feb2014 01apr2014 2 mag1 01apr2014 01jul2014

Lucas · ‎10-06-2011

Hi, Thank you all for responses. @Tom's solution is very usefull but i could be done with less coding. data dates; input date ddmmyy10. number @@; format date ddmmyy10.; cards; 10-10-2011 697 10-10-2011 122 09-10-2011 607 08-10-2011 607 07-10-2011 122 07-10-2011 607 run; data trans; input number @@ ; cards; 529 122 697 607 888 run; data lookup ; if _n_=1 then do; if 0 then set dates; declare Hash H(dataset: "dates"); H.DefineKey ('number'); H.DefineData ('date'); H.DefineDone (); /* complete hash table definition */ end; set trans; if h.find() then date=.; put number= date=; run; According to the documentation (link from @Scott Bass): If the data set contains duplicate keys, by default, the first instance is stored in the hash object; subsequent instances are ignored. To store the last instancein the hash object, use the DUPLICATE argument tag. The DUPLICATE argumenttag also writes an error to the SAS log if there is a duplicate key. As I understand, during loading hash the source table is reading sequentially. Sorting before loading hash should be enough. I hope

Lucas · ‎10-02-2011

But when I make an index key on new column 'count' I can't join on column 'phone'. As I said before I have table with columns: created_date, phone. It's a lookup table. created_date phone 10-10-2011 607 10-10-2011 122 09-10-2011 607 08-10-2011 607 07-10-2011 122 07-10-2011 607 ... ... I have also transaction table with column phone. phone 529 122 697 .... I want to join these two tables using hash. Is it possible to make index key on two columns created_date + phone (then could use tag 'ordered' ), but join (with find() method) only on one column (phone).

Lucas · ‎10-02-2011

Thanks for response. My question was not precise. I will try ask one more time I'm loading sorted data set into hash. Data set is sorted by 'created_date' column (descending). Hash key is on column 'phone'. Vaules in 'phone' contains duplicates. created_date phone 10-10-2011 607 10-10-2011 122 09-10-2011 607 08-10-2011 607 07-10-2011 122 07-10-2011 607 I want to be sure that in hash always will be phone with the latest created_date. Because data set is large I don't want to make any additional transformations before loading it into hash. My question was not precise

Lucas · ‎10-01-2011

Hello, I'm loading sorted data set into hash. I need to have data in hash exactly in the same order as in data set. My question is: Are the observations always stored in the hash in the same order as in data set? Because data are not sorted by the hash key, I can't use tag- ordered in DECLARE statement.

Online Status	Offline
Date Last Visited	‎09-07-2020 09:51 AM

Re: time period determination using PROC SQL

time period determination using PROC SQL

Re: Creating combinations of items in the list

Creating combinations of items in the list

Re: dynamic list of tables in a view definition

dynamic list of tables in a view definition

Re: Search for common periods for an interval of dates

Re: Search for common periods for an interval of dates

Re: Search for common periods for an interval of dates

Re: Search for common periods for an interval of dates

Re: time period determination using PROC SQL

time period determination using PROC SQL

Re: Creating combinations of items in the list

Creating combinations of items in the list

Re: dynamic list of tables in a view definition

dynamic list of tables in a view definition

Re: Search for common periods for an interval of dates

Re: Search for common periods for an interval of dates

Re: Search for common periods for an interval of dates

Re: Search for common periods for an interval of dates

Search for common periods for an interval of dates

Re: Question about Hash Tables

Re: Question about Hash Tables

Re: Question about Hash Tables

Question about Hash Tables