cust_no date obs_window point abc123 201801 1 0 abc123 201802 1 1 abc123 201803 1 2 abc123 201804 1 3 abc123 201805 2 0 abc123 201806 2 1 abc123 201807 2 2 def456 201803 1 0 def456 201804 1 1 def456 201805 2 0 def456 201806 2 1 def456 201807 2 2   Hello guys, let's assume the above is my dataset, and the "point" is the column I want to create. In this case, I wish to create the input for the "point" column starting with 0 for the first observed observation of each customer, and +1 thereafter. However, if obs_window is different, then the point will restart again for a customer.   the current code I'm using is as below: data mydataset; set dataset; by cust_no; if first.cust_no then point = 0; else point +1;   in this case, how should I tune the above code? ... View more

Hi all, I am a beginner in SAS, so please bear with my question. Basically, I want to look up the value for my dataset from another dataset, but not sure how to do it in a faster way (as the data is millions). I tried using proc sql but it took very long and failed.    Let's say I created a new column for my current dataset as below: let's say this dataset called 'abc' customer date 112   114   203   158     Then, the lookup dataset called 'lookup' is as below: customer date 112 201002 114 201004 203 201006 158 201007 203 200901 158 200704 As you can observe from the lookup table above, there are duplicate customer numbers with different dates. However, what I wanna do is to look up the earliest date from the lookup table and append them into the 'abc' dataset.   The below shows the expected output I wish to have for abc table: customer date 112 201002 114 201004 203 200901 158 200704   Appreciate if anyone can help (this is million row dataset) ... View more

I would like to ask how can I code to fill in the missing date between the beginning period and the most recent date available for each customer as illustrated below:   assuming below is my data: Date Customer ID Sales Credit Jan-17 12345 xx xx Feb-17 12345 xx xx Mar-17 12345 xx xx Jun-17 12345 xx xx Jul-17 12345 xx xx Aug-17 12345 xx xx Sep-17 12345 xx xx Oct-17 12345 xx xx Nov-17 12345 xx xx Dec-17 12345 xx xx Jan-17 7891 xx xx Feb-17 7891 xx xx Mar-17 7891 xx xx Apr-17 7891 xx xx Jun-17 7891 xx xx Jul-17 7891 xx xx Sep-17 7891 xx xx Oct-17 7891 xx xx   I want to generate something like this instead: Date Customer ID Sales Credit Jan-17 12345 xx xx Feb-17 12345 xx xx Mar-17 12345 xx xx Apr-17 12345 . . May-17 12345 . . Jun-17 12345 xx xx Jul-17 12345 xx xx Aug-17 12345 xx xx Sep-17 12345 xx xx Oct-17 12345 xx xx Nov-17 12345 xx xx Dec-17 12345 xx xx Jan-17 7891 xx xx Feb-17 7891 xx xx Mar-17 7891 xx xx Apr-17 7891 xx xx May-17 7891 . . Jun-17 7891 xx xx Jul-17 7891 xx xx Aug-17 7891 . . Sep-17 7891 xx xx Oct-17 7891 xx xx   As you can see from the data, the beginning date for customer 12345 is starting from Jan 2017 and the most recent date is Dec 2017, however, there are missing dates in between the beginning date and the latest date (April and May 2017). While for customer 7891, the beginning date is starting from Jan 2017 and the most recent date is Oct 2017, but there are also missing dates in between the beginning date and the latest date (May and Aug 2017). Hence, in this case, I just want to fill in the missing dates between their beginning date and their latest date available. In other ways, meaning that I want customer 12345 to have date from Jan 2017 to Dec 2017 while customer 7891 to have date from Jan 2017 to Oct 2017 only, with the missing date's and its data fields imputed with missing value ".".  I tried to use proc expand initially (tested on few customers and it succeed), however, proc expand does not allow for big data (around 40millions), and it will show workspace error/failed. Hence, is there any alternative way to fill in the missing date in between the beginning date and most recent date available for a customer (SAS)?  ... View more