About dolldata

dolldata · ‎04-29-2021

Thank you - this is exactly what I needed for categories. Is there something similar that can be done with date? Like I mentioned when outputting results by day, but there won't be records on holidays, but still want a line for them. Or do I need to create a new format for the entire date range I want to see? Again, THANK YOU!

dolldata · ‎04-29-2021

I am sure this question has been asked and answered, but I've been searching for hours and am obviously not using the correct terms to find the answer. How can I make sure a row is represented in output, even if there is no value? For example, when pulling orders by day, comparing two channels (website vs actual store). The website will have orders for every date as it never "closes". But, the stores will have some days missing due to closures for holiday. I am trying to keep the output consistent between channels. So, for example, in the store data, there would be a row for 4/4/2021 (Easter), but then the values of order or demand or whatever would all be blank. Similarly, when I create my own format, I thought there was a way to output all values for that format, even if there is no data for it. For example, in the below code, there are no data lines for April or September, but would want the output to still have a line for them: proc format; value months 1 = 'JAN' 2 = 'FEB' 3 = 'MAR' 4 = 'APR' 5 = 'MAY' 6 = 'JUN' 7 = 'JUL' 8 = 'AUG' 9 = 'SEP' 10 = 'OCT' 11 = 'NOV' 12 = 'DEC' ; run; data example; input month orders; datalines; 1 25 1 36 2 95 2 86 2 74 3 25 3 91 5 53 5 58 6 29 6 41 6 58 7 79 7 46 8 13 8 82 10 94 10 52 10 45 11 78 11 47 12 45 12 39 ; run; proc summary data = example sum missing nway; class month; format month months.; var orders; output out = bymonth (drop = _type_ _freq_) sum = ; run; proc print data = bymonth noobs; run; Output: month orders JAN 61 FEB 255 MAR 116 MAY 111 JUN 128 JUL 125 AUG 95 OCT 191 NOV 125 DEC 84 Desired output: month orders JAN 61 FEB 255 MAR 116 APR MAY 111 JUN 128 JUL 125 AUG 95 SEP OCT 191 NOV 125 DEC 84

dolldata · ‎05-15-2018

I am working with SAS datasets. I kept getting an error that the first instance of the 'want2' table didn't exist: 78 data want2(drop=_:); 79 80 if _n_=1 then 81 do; 82 if 0 then set want2(keep=customer order_date demand rename=(order_date=_order_date demand=_demand)); ERROR: File WORK.WANT2.DATA does not exist. 83 dcl hash h1(dataset:'want2(keep=customer order_date demand rename=(order_date=_order_date demand=_demand))', 83 ! multidata:'y'); 84 h1.defineKey('customer'); 85 h1.defineData('_order_date','_demand'); 86 h1.defineDone(); 87 end; 88 89 set have; 90 91 demand366_calc=0; 92 demand732_calc=0; 93 do while(h1.do_over() eq 0); 94 if order_date > _order_date then 95 do; 96 if _order_date >= order_date-366 then demand366_calc=sum(demand366_calc,_demand); 97 if _order_date >= order_date-732 then demand732_calc=sum(demand732_calc,_demand); 98 end; 99 end; 100 run; After trial and error, replacing 'want2' with 'have' within the datastep made it work: data want2(drop=_:); if _n_=1 then do; if 0 then set have(keep=customer order_date demand rename=(order_date=_order_date demand=_demand)); dcl hash h1(dataset:'have(keep=customer order_date demand rename=(order_date=_order_date demand=_demand))', multidata:'y'); h1.defineKey('customer'); h1.defineData('_order_date','_demand'); h1.defineDone(); end; set have; demand366_calc=0; demand732_calc=0; do while(h1.do_over() eq 0); if order_date > _order_date then do; if _order_date >= order_date-366 then demand366_calc=sum(demand366_calc,_demand); if _order_date >= order_date-732 then demand732_calc=sum(demand732_calc,_demand); end; end; run; I have spot-checked several records and everything appears to be calculating correctly. This code is incredible - processes all 7.4 million records in just 15 seconds. I definitely need to spend more time understanding hash processing! Thank you again for the help!

dolldata · ‎05-15-2018

I hate to add another reply to the string, especially after accepting the solution, but is there a way to speed up the calculations? I am running this on about 7.4 million records, summarizing the activity of 974K customers (we are running SAS Studio release 3.6, with the processing on a server). I realize it should take a long time, as it is doing so many calculations on each customer, but at the current rate, it will take about three days to get the processing done. Which may just be the way it is. Just curious if there are ideas on how to optimize that?

dolldata · ‎05-11-2018

Hi Doug, I know it's been awhile since your response, but we've still been working through this issue. I realize that doing the entire process in Miner is simpler and preferred. However, we only have a desktop version of Miner and are not licensed to connect it to our server. Therefore, scoring our entire database with Miner is not an option at this point. That is why we want to be able to pull the binning information out, so that we can code it back into Base SAS, where we are connected to our server, and can then score the full database. I also realize that for many, using Miner as a bit of a black box is ideal, but we have to be able to explain our models in detail, so it's rather hard to do when we can't see much of the information. Recently, I have been working in the Interactive Binning Node and like the capabilities within it, except of course for still not being able to see the WOE information. The results do create the graph with the Calculated WOE / New WOE, but I've yet to figure out how to see what those actual values are (the graph only details the number of events for each bin, not the WOE values). Is there some output that tells me these values?

dolldata · ‎05-10-2018

Apologies for not putting the data in a step - that was a big miss. But thank you for this solution - it works perfectly! I had not heard of the coalesce function, but will definitely be researching it more.

dolldata · ‎05-10-2018

Hello, I am trying to summarize customer behavior to determine on each one of their orders if they were a new customer on that order or an existing customer. To make things more difficult, I want to break the existing customers into more specific groups, based on if they had purchased within a year before or not. Furthermore, if they had purchased in the previous year, break them apart by how much they spent in that previous year. I have a dataset of customers and all of their orders, and want to leave it at the order level (one line per order, not one line per customer). For an example household I already have the first four columns, but want to calculate the last two columns (demand366 and demand732) to be able to then classify existing customers. customer first_order order_date demand demand366 demand732 123 03/15/15 03/15/15 $36.25 $0.00 $0.00 123 03/15/15 03/18/15 $50.00 $36.25 $36.25 123 03/15/15 11/11/15 $126.00 $86.25 $86.25 123 03/15/15 02/06/16 $72.00 $212.25 $212.25 123 03/15/15 10/20/16 $186.95 $198.00 $284.25 123 03/15/15 10/15/17 $45.00 $186.95 $384.95 123 03/15/15 01/27/18 $92.00 $45.00 $303.95 So, for customer 123, on their first order (3/15/15), they would have $0 in demand for the 12 months (366 days) and 24 months (732 days) PRIOR to that first order. On their next order, 3/18/15, they would have spent $36.25 PRIOR to that order (both for 12 and 24 months). To get more complicated, if you look at the order on 10/20/16, only the prior two orders were within 12 months, so the demand366 total would be $198.00 ($126 + $72), but the 24 month total would include all four prior orders ($36.25 + $50 + $126 + $72 = $284.25). I'm struggling on how to get SAS to calculate this, and if do loops or first./last. or something within proc sql would work best...or some combination of all of those methods. I know I am over-complicating this in my mind...I feel there should be a fairly easy way to do this? Any suggestions would be greatly appreciated!

dolldata · ‎10-04-2017

Running Enterprise Miner 14.1 with SAS 9.4 Desktop. I am using the Transform Variables node, trying to bin some variables. In the 'Variables - Trans' window, I change the 'Method' to Optimal Binning for the desired variables, but after running the node, it does not bin any of the variables. If I instead set the method to Log 10, it does appear to perform the transformation. Is there some setting I need to change? Or is there a rule why it might not bin a variable?

dolldata · ‎10-03-2017

We are currently struggling with Optimal Binning in Enterprise Miner. Though we have used base SAS for years, when modeling, we always did our binning and variable selection within the Angoss Knowledge Studio program, but recently converted to Enterprise Miner (version 14.1). In Angoss, through Optimal Binning and Weight of Evidence (WOE), the software essentially built a decision tree for each variable, basing the bins on the variable's relation to the binary target variable. The software chose the optimal number of bins, but we could go in and tweak them as we chose. Then we could code a single variable from the bins, where the values were the weight of evidence. For example: /*Sample of a binned variable based on WOE*/ if transactions < 0 then bin_transactions = -0.0617; else if transactions < 1 then bin_transactions = -0.4566; else if transactions < 2 then bin_transactions = -0.0617; else if transactions < 7 then bin_transactions = 0.1214; else if transactions < 16 then bin_transactions = 0.3625; else if transactions < 27 then bin_transactions = 0.5316; else if transactions >= 27 then bin_transactions = 0.9163; We would then code these binned variables into base SAS and, along with other top variables we had selected, run a PROC LOGISTIC to create our model, which would result in a single coefficient for the binned variable. /*Reduced example of the model equation (for inclusion in logistic conversion)*/ Y1 = - 0.00131*recency + 0.2438*bin_transactions /*binned variable*/ + 0.1007*grandparent - 1.5645; p = 1/(1 + EXP(-Y1)); Not the most efficient method, but it created beautiful models! Using Enterprise Miner for the entire process, including the regression, we can only seem to create indicator variables for each bin within a variable, and then have a coefficient for each bin. So the transactions variable referenced above would now be seven unique variables, all with their own coefficients, which doesn't seem ideal. Is there any way to get this same type of information within Enterprise Miner? After reading threads here, and after discussions we originally had with SAS when converting, we haven't really found a way to do this.

dolldata · ‎09-06-2017

In case anyone else runs into a similar issue, SAS Technical Support was able to give us a solution! A modified explanation from them: "Starting in 9.4M2 when you are accessing Oracle 12c or later and your code includes (obs= ), a "fetch first n rows" is appended to the query that is submitted to the database. This results in Oracle only providing the [first n observations] to SAS which explains why the first [n] rows worked but [then] could no longer find the match. Another piece to this issue is...using a function that is not passed to the database automatically. [Running] with [the SQL_FUNCTIONS= option in your LIBNAME statement], the WHERE was passed to the database so the obs= didn't affect the pulling of results." So, we have added the "sql_functions = all" to our database libname statement, and that works. Support also provided us these helpful links: http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#n0k1cax0gzsdxdn1a3466l1gwtk9.htm http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#p0f64yzzxbsg8un1uwgstc6fivjd.htm

dolldata · ‎08-28-2017

@Reeza wrote: This is likely a SAS tech support question. One thing though - was the data imported in both or are you accessing a server or stored data somewhere? If it's imported moving from Windows to a Unix server that could be part of the issue. Hi Reeza, Both attempts were accessing the same database on a server. I'll submit a ticket to tech support as well. Thank you!

dolldata · ‎08-28-2017

Hello, We are running into an interesting problem...we are currently switching from SAS 9.2 (server) / 9.3 (desktop) to running SAS 9.4 on a virtual server and using SAS Studio. [The upgrade has been a long time coming! :-)] With the "old" SAS, the following query works perfectly: rsubmit; proc print data = db.customers (obs = 10); where substr(postal_code,1,5) = '04107'; var postal_code state; run; endrsubmit; LOG: NOTE: There were 10 observations read from the data set DB.CUSTOMERS. WHERE SUBSTR(postal_code, 1, 5)='04107'; NOTE: The PROCEDURE PRINT printed page 1. NOTE: PROCEDURE PRINT used (Total process time): real time 3.38 seconds cpu time 0.06 seconds OUTPUT: Obs POSTAL_CODE STATE 75 041072704 ME 8284 041072615 ME 15263 041079682 ME 23753 041079603 ME 25667 041071623 ME 29707 041072615 ME 34185 041071219 ME 45532 041070257 ME 58933 041071156 ME 68820 041071423 ME However, in SAS Studio, on SAS 9.4, the following query yields zero observations, but we don't see any errors or problems in the notes: proc print data = db.customers (obs = 10); where substr(postal_code,1,5) = '04107'; var postal_code state; run; LOG: NOTE: No observations were selected from data set DB.CUSTOMERS. NOTE: PROCEDURE PRINT used (Total process time): real time 0.06 seconds cpu time 0.00 seconds If we take the SUBSTR out and pull a specific postal code (all 9 characters), the query works: proc print data = db.customers (obs = 10); where postal_code = '041072704'; var postal_code state; run; LOG: NOTE: PROCEDURE PRINT used (Total process time): real time 0.20 seconds cpu time 0.09 seconds OUTPUT: Obs POSTAL_CODE STATE 1 2 3 041072704 ME 041072704 ME 041072704 ME The SUBSTR funciton works correctly within a PROC SQL statement. It's rare that we would use the SUBSTR function like this (in a PROC PRINT), but the issue has us worried that something has changed between the versions that could cause us to run into other issues, too. Anyone have ideas on what we might be missing? THANK YOU!

dolldata · ‎03-01-2017

Hi @Patrick, Yes, a bundle is a defined set of SKUs within the same order. To @mkeintz's question, a single SKU could be an element of more than one bundle (which makes this even more "fun"). So, in creating a reference table, I'm struggling with how to accomidate that fact. My current thought is to just define a macro group for each bundle: %let b1 = ('TGD25', 'TGD38', 'XGD02'); %let b2 = ('TGD25', 'TGD29'); ... %let b68 = ('PND56', 'PND59'); A very small sample dataset: data mydata; input order_id line_id SKU $; datalines; 12345 1234 TGD25 12345 1235 TGD38 12345 1236 CLX30 12345 1237 XGD02 12345 1238 BFD01 67891 2345 BLK89 67891 2346 TGD25 67891 2347 CLK89 67891 2348 TGD25 67891 2349 TGD29 56789 4512 TGD38 56789 4513 BLK78 56789 4514 TGD25 56789 4515 CLT98 56789 4516 BLH67 56789 4517 CLX89 ; run; In this sample, ---order 12345 has one bundle (as defined in &b1: 'TGD25', 'TGD38', 'XGD02') ---order 67891 has one bundle (as defined in &b2: 'TGD25', 'TGD29'), but also has an extra sku ('TGD25') that should not be counted ---order 56789 has two valid skus for &b1 ('TGD25', 'TGD38'), but does not contain the third and therefore has no bundle. To both your points, I think the rules are: 1. All SKUs must be on a single order 2. There can be more than one bundle per order, but each SKU still just needs a 1/0 flag 3. Partial bundles (extra SKUs not completing a bundle) should not be flagged I'd like to keep everything at the line level. The very end result, after this gets merged in with lots of other orders, is that I could sum the demand of lines that are flagged as part of a full bundle. For this sample, the desired final dataset would be: order_id line_id sku bundle 12345 1234 TGD25 1 12345 1235 TGD38 1 12345 1236 CLX30 0 12345 1237 XGD02 1 12345 1238 BFD01 0 67891 2345 BLK89 0 67891 2346 TGD25 0 67891 2347 CLK89 0 67891 2348 TGD25 1 67891 2349 TGD29 1 56789 4512 TGD38 0 56789 4513 BLK78 0 56789 4514 TGD25 0 56789 4515 CLT98 0 56789 4516 BLH67 0 56789 4517 CLX89 0 I have been trying to do a datastep that counts the number of each SKU on the order, then takes the minimum for those counts (to get how many valid bundles there are), but struggle with how to keep the unique line_ids through all that so I can go back and flag the lines. For example, with order 67891 above, if I summed by order_id, by SKU, I would have count_TGD25 = 2 and count_TGD29 = 1. If I take the min(count_TGD25, TGD29), and get 1, then I know I have one full bundle...but then how to I take that back to flag the first instance of the 'TGD25' SKU, but not the second instance?

dolldata · ‎02-28-2017

I've been swirling on how to complete this, so thought I'd ask some better minds! I'm trying to flag order lines that are part of a discounted bundle. I have product data at the line level for each order, and the bundles are identified by if they contain the necessary SKUs. I want to keep the data at the line level, and add a binary flag for lines that are members of a complete bundle. I have 68 bundles to identify, and they can range from two to five SKUs making up a bundle. I'm thinking I need some sort of data step, using first. logic, and then maybe using a macro to run it for each unique bundle. Below is an example of what I'm looking for: ORDER ID LINE ID SKU BUNDLE 183567 681996 TGD25 1 183567 682001 TGD27 0 183567 681997 TGD29 1 183567 682002 XGD04 0 183567 681998 GJD47 0 I already have the data in the three left columns and just want to add the right column ("Bundle"). The SKUs TGD25 and TGD29 make up a valid bundle. To complicate things, the SKU TGD27 is part of a different bundle, but NOT this one. Also, there may be cases where the order might contain two of one SKU needed, and one of the others (so TGD25, TGD25, TGD29), but I would only want to flag one of the TGD25 SKU's, since there is only one complete bundle. Any ideas on how to accomplish this would be GREATLY appreciated! Please let me know if I can add any more details.

dolldata · ‎01-11-2017

Hi RW9! Yes, we are transferring from Windows to Unix...modifying the FTP transfer settings (from "Binary" to "Text") worked perfectly. Also, your recommendation of using the datastep import, modified from the proc import, was very useful--and saves us several steps. Thank you so much for the explanation on this!

Online Status	Offline
Date Last Visited	‎01-12-2022 07:00 PM

Re: Output Rows Even When No Value

Output Rows Even When No Value

Re: Data rollup - sum previous rows if criteria are met

Re: Data rollup - sum previous rows if criteria are met

Re: Optimal Binning and WOE for binary target variable

Re: Data rollup - sum previous rows if criteria are met

Data rollup - sum previous rows if criteria are met

Enterprise Miner Transform Variables not transforming

Optimal Binning and WOE for binary target variable

Re: Using SUBSTR function in SAS 9.4 with SAS Studio

Re: Output Rows Even When No Value

Re: Optimal Binning and WOE for binary target variable

Re: Data rollup - sum previous rows if criteria are met

Re: Data rollup - sum previous rows if criteria are met

Re: Enterprise Miner Transform Variables not transforming

Re: Output Rows Even When No Value

Output Rows Even When No Value

Re: Data rollup - sum previous rows if criteria are met

Re: Data rollup - sum previous rows if criteria are met

Re: Optimal Binning and WOE for binary target variable

Re: Data rollup - sum previous rows if criteria are met

Data rollup - sum previous rows if criteria are met

Enterprise Miner Transform Variables not transforming

Optimal Binning and WOE for binary target variable

Re: Using SUBSTR function in SAS 9.4 with SAS Studio

Re: Using SUBSTR function in SAS 9.4 with SAS Studio

Using SUBSTR function in SAS 9.4 with SAS Studio

Re: Recursive pass through data step to identify parts of a group

Recursive pass through data step to identify parts of a group

Re: Importing Data - Direct from the network / phantom character getti...

SAS Analytics Explorers