Hello, I am getting the following error messages when trying to merge two datasets. One of the datasets I am getting from a csv file, so maybe the issue could be there? I was trying to specify the length of the PID variable for the redcap_sort dataset from the redcap one, which is the one we got from the csv file. However, I keep getting messages that the variable has multiple lengths and it keeps truncating the data.   Any PID after 999 gets shortened. So 1000 and 1001 become 100, 1010 becomes 101, etc.   Any help or a nudge in the right direction would be greatly appreciated, thank you so much.   ... View more

I'm trying to follow the code on this site Test for the equality of two proportions in SAS - The DO Loop for the section called A chi-square test for association in SAS. I basically need to compare the proportion in one area which was tested for something to the proportion in another area which was tested and see if they are significantly different proportions, but I can't get the code to work right. I get this error:     NOTE: Invalid data for N in line 79 1-6. RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0 79 CountyB Yes 71 Group=CountyA Seq=No N=. _ERROR_=1 _N_=2 NOTE: SAS went to a new line when INPUT statement reached past the end of a line.   My full code is:    data underfive; length Group $15 Test $3; input Group Test N; datalines; CountyA Yes 55 CountyA No 45027 CountyB Yes 71 CountyB No 311726;   Once I had that in I figured I would run this:   proc freq data=underfive order=data; weight N; tables Group*Test/chisq; run; ... View more

Hi, all. My last post (https://communities.sas.com/t5/SAS-Programming/Calculate-How-Prior-Bet-Profits-Losses-Influence-Future-Betting/m-p/928006#M365143) went unsolved (thanks very much to those who gave it a look). I am clarifying here and asking for additional help. Unnecessary columns have been removed and desired calculations are now described within the datalines.   Description: When a gambler decides how much to bet, their decision is influenced by their prior wager outcomes (perhaps they start to double down if they have sustained losses, for example), and I am trying to examine how NFL gamblers' prior betting profits/losses influence their next-round-of-betting behavior.  I need a rolling sum of the profits/losses from just the last very round of completed NFL events as shown below.   data WORK.bets (drop=product_account_id bet_id eleven_digit_account_id net_stake); infile datalines; input product_account_id:$10. bet_id:$15. eleven_digit_account_id:$11. net_stake:3. bet_date:mmddyy10. bet_time:time8. bet_day_of_week:$10. event_date:mmddyy10. event_time:time8. event_day_of_week:$10. bet_profit:5. desired_output:5.; format bet_date mmddyy10. bet_time time8. event_date mmddyy10. event_time time8.; *Note: games (aka 'events') last three hours from start to finish; *Note: there are many gamblers, each with a unique product_account_id, so summing needs to begin anew when a new product_account_id arrives; datalines; 12101170 81758848.516 00012101170 2 10/9/2023 20:29 Monday 10/9/2023 20:16 Monday 2 . 12101170 81758939.516 00012101170 3 10/9/2023 20:30 Monday 10/9/2023 20:16 Monday 2.4 . 12101170 81759255.516 00012101170 2 10/9/2023 20:34 Monday 10/9/2023 20:16 Monday 1.68 . 12101170 81760925.516 00012101170 3 10/9/2023 20:48 Monday 10/9/2023 20:16 Monday 2.61 . 12101170 81764937.516 00012101170 3 10/9/2023 21:26 Monday 10/9/2023 20:16 Monday -3 . 12101170 81768542.516 00012101170 3 10/9/2023 22:03 Monday 10/9/2023 20:16 Monday 2.64 . 12101170 82231252.516 00012101170 3 10/14/2023 14:38 Saturday 10/15/2023 13:01 Sunday 2.73 8.33 *the Monday events have expired and the payouts are now known, desired_output is the sum of the first six rows of bets* 12101170 82231251.516 00012101170 3 10/14/2023 14:38 Saturday 10/15/2023 13:05 Sunday 2.73 8.33 *the last known event outcomes were again last Monday, same calculation* 12101170 82231250.516 00012101170 3 10/14/2023 14:38 Saturday 10/15/2023 13:05 Sunday 2.46 8.33 *same here* 12101170 82231253.516 00012101170 3 10/14/2023 14:38 Saturday 10/15/2023 16:26 Sunday 2.85 8.33 *same here* 12101170 82231254.516 00012101170 3 10/14/2023 14:38 Saturday 10/15/2023 16:27 Sunday -3 8.33 *same here* 12101170 82231345.516 00012101170 3 10/14/2023 14:39 Saturday 10/15/2023 13:05 Sunday 2.46 8.33 *same here* 12101170 82231346.516 00012101170 3 10/14/2023 14:39 Saturday 10/15/2023 13:05 Sunday 2.73 8.33 *same here* 12101170 82231347.516 00012101170 3 10/14/2023 14:39 Saturday 10/15/2023 16:26 Sunday 2.85 8.33 *same here* 12101170 82231348.516 00012101170 3 10/14/2023 14:39 Saturday 10/15/2023 16:27 Sunday -3 8.33 *same here* 12101170 82231466.516 00012101170 3 10/14/2023 14:40 Saturday 10/15/2023 13:05 Sunday 2.73 8.33 *etc....* 12101170 82231465.516 00012101170 3 10/14/2023 14:40 Saturday 10/15/2023 13:05 Sunday 2.46 8.33 12101170 82231467.516 00012101170 3 10/14/2023 14:40 Saturday 10/15/2023 16:26 Sunday 2.85 8.33 12101170 82348229.516 00012101170 3 10/15/2023 7:49 Sunday 10/15/2023 9:35 Sunday -3 8.33 12101170 82348234.516 00012101170 3 10/15/2023 7:49 Sunday 10/15/2023 13:01 Sunday -3 8.33 12101170 82348231.516 00012101170 3 10/15/2023 7:49 Sunday 10/15/2023 13:01 Sunday 2.61 8.33 12101170 82348230.516 00012101170 3 10/15/2023 7:49 Sunday 10/15/2023 13:03 Sunday -3 8.33 12101170 82348233.516 00012101170 3 10/15/2023 7:49 Sunday 10/15/2023 13:04 Sunday 2.55 8.33 12101170 82348235.516 00012101170 3 10/15/2023 7:49 Sunday 10/15/2023 13:05 Sunday 2.94 8.33 12101170 82348232.516 00012101170 3 10/15/2023 7:49 Sunday 10/15/2023 13:05 Sunday 2.73 8.33 12101170 82348267.516 00012101170 3 10/15/2023 7:50 Sunday 10/15/2023 13:01 Sunday 2.61 8.33 12101170 82348270.516 00012101170 3 10/15/2023 7:50 Sunday 10/15/2023 13:01 Sunday -3 8.33 12101170 82348266.516 00012101170 3 10/15/2023 7:50 Sunday 10/15/2023 13:03 Sunday -3 8.33 12101170 82348269.516 00012101170 3 10/15/2023 7:50 Sunday 10/15/2023 13:04 Sunday 2.55 8.33 12101170 82348271.516 00012101170 3 10/15/2023 7:50 Sunday 10/15/2023 13:05 Sunday 2.94 8.33 12101170 82348268.516 00012101170 3 10/15/2023 7:50 Sunday 10/15/2023 13:05 Sunday 2.73 8.33 12101170 82348305.516 00012101170 3 10/15/2023 7:51 Sunday 10/15/2023 13:01 Sunday -3 8.33 12101170 82348307.516 00012101170 3 10/15/2023 7:51 Sunday 10/15/2023 13:01 Sunday 2.85 8.33 12101170 82348308.516 00012101170 3 10/15/2023 7:51 Sunday 10/15/2023 13:03 Sunday -3 8.33 12101170 82348306.516 00012101170 3 10/15/2023 7:51 Sunday 10/15/2023 13:05 Sunday 2.94 8.33 12101170 82348304.516 00012101170 3 10/15/2023 7:51 Sunday 10/15/2023 13:05 Sunday 2.73 8.33 12101170 82439386.516 00012101170 3 10/15/2023 13:14 Sunday 10/15/2023 13:04 Sunday -3 -3 *this bet was placed on Sunday at ~1:00 p.m., the last event in which the gambler experienced a payout began Sunday at 9:35 a.m. and ended Sunday at 12:35 p.m., that bet's profit was -3* 12101170 82439455.516 00012101170 3 10/15/2023 13:14 Sunday 10/15/2023 13:01 Sunday 2.73 -3 *same here* 12101170 82439531.516 00012101170 3 10/15/2023 13:14 Sunday 10/15/2023 13:05 Sunday 2.73 -3 *same here* 12101170 82478787.516 00012101170 3 10/15/2023 16:02 Sunday 10/15/2023 16:06 Sunday 2.46 33.03 *this bet was placed on Sunday at ~4:00 p.m., the most recent profits are from events played on Sunday from ~1:00 to ~4:00, profits are the sum of rows 7-18 plus rows 20-36 (profit from row 19 has already been captured)* 12101170 82478786.516 00012101170 3 10/15/2023 16:02 Sunday 10/15/2023 16:26 Sunday 2.73 33.03 12101170 82478788.516 00012101170 3 10/15/2023 16:02 Sunday 10/15/2023 16:26 Sunday 2.73 33.03 12101170 82478789.516 00012101170 3 10/15/2023 16:02 Sunday 10/15/2023 16:27 Sunday -3 33.03 12101170 82478974.516 00012101170 3 10/15/2023 16:02 Sunday 10/15/2023 16:06 Sunday 2.46 33.03 12101170 82478973.516 00012101170 3 10/15/2023 16:02 Sunday 10/15/2023 16:26 Sunday 2.73 33.03 12101170 82478975.516 00012101170 3 10/15/2023 16:02 Sunday 10/15/2023 16:27 Sunday -3 33.03 12101170 82479827.516 00012101170 6 10/15/2023 16:03 Sunday 10/15/2023 16:26 Sunday 5.46 33.03 12101170 82479826.516 00012101170 6 10/15/2023 16:03 Sunday 10/15/2023 16:27 Sunday -6 33.03 12101170 82482465.516 00012101170 3 10/15/2023 16:07 Sunday 10/15/2023 16:26 Sunday 2.73 33.03 12101170 82493073.516 00012101170 2 10/15/2023 16:34 Sunday 10/15/2023 16:26 Sunday 1.92 33.03 12101170 82550551.516 00012101170 2 10/15/2023 20:57 Sunday 10/15/2023 20:23 Sunday -2 11.22 *this bet was placed on Sunday at ~8:00 p.m., the last profits were from games played earlier that day at 4:00 p.m. (rows 40-50)* 12101170 82553228.516 00012101170 2 10/15/2023 21:23 Sunday 10/15/2023 20:23 Sunday -2 11.22 *same here* 12101170 82555259.516 00012101170 2 10/15/2023 21:44 Sunday 10/15/2023 20:23 Sunday -2 11.22 *same here* 12101170 82646184.516 00012101170 3 10/16/2023 20:25 Monday 10/16/2023 20:16 Monday -3 -6 12101170 82647533.516 00012101170 3 10/16/2023 20:34 Monday 10/16/2023 20:16 Monday -3 -6 12101170 82647545.516 00012101170 6 10/16/2023 20:34 Monday 10/16/2023 20:16 Monday -6 -6 12101170 82899789.516 00012101170 2 10/19/2023 20:23 Thursday 10/19/2023 20:16 Thursday 1.96 -12 12101170 82904693.516 00012101170 3 10/19/2023 20:59 Thursday 10/19/2023 20:16 Thursday 2.64 -12 12101170 82906147.516 00012101170 3 10/19/2023 21:08 Thursday 10/19/2023 20:16 Thursday -3 -12 12101170 83235976.516 00012101170 3 10/22/2023 9:23 Sunday 10/22/2023 13:02 Sunday -3 1.6 12101170 83235974.516 00012101170 3 10/22/2023 9:23 Sunday 10/22/2023 13:03 Sunday -3 1.6 12101170 83236035.516 00012101170 3 10/22/2023 9:24 Sunday 10/22/2023 13:02 Sunday -3 1.6 12101170 83236033.516 00012101170 3 10/22/2023 9:24 Sunday 10/22/2023 13:03 Sunday -3 1.6 12101170 83236190.516 00012101170 3 10/22/2023 9:25 Sunday 10/22/2023 13:02 Sunday -3 1.6 12101170 83237824.516 00012101170 3 10/22/2023 9:36 Sunday 10/22/2023 13:01 Sunday -3 1.6 12101170 83237827.516 00012101170 3 10/22/2023 9:36 Sunday 10/22/2023 13:02 Sunday -3 1.6 12101170 83237823.516 00012101170 3 10/22/2023 9:36 Sunday 10/22/2023 13:03 Sunday -3 1.6 12101170 83237825.516 00012101170 3 10/22/2023 9:36 Sunday 10/22/2023 13:05 Sunday 2.64 1.6 12101170 83237829.516 00012101170 3 10/22/2023 9:36 Sunday 10/22/2023 16:06 Sunday -3 1.6 12101170 83237922.516 00012101170 3 10/22/2023 9:36 Sunday 10/22/2023 13:01 Sunday -3 1.6 12101170 83237924.516 00012101170 3 10/22/2023 9:36 Sunday 10/22/2023 13:02 Sunday -3 1.6 12101170 83237921.516 00012101170 3 10/22/2023 9:36 Sunday 10/22/2023 13:03 Sunday -3 1.6 12101170 83237926.516 00012101170 3 10/22/2023 9:36 Sunday 10/22/2023 16:06 Sunday -3 1.6 12101170 83238000.516 00012101170 3 10/22/2023 9:37 Sunday 10/22/2023 13:01 Sunday -3 1.6 12101170 83238002.516 00012101170 3 10/22/2023 9:37 Sunday 10/22/2023 13:02 Sunday -3 1.6 12101170 83237999.516 00012101170 3 10/22/2023 9:37 Sunday 10/22/2023 13:03 Sunday -3 1.6 12101170 83238004.516 00012101170 3 10/22/2023 9:37 Sunday 10/22/2023 16:06 Sunday -3 1.6 12101170 83238165.516 00012101170 3 10/22/2023 9:38 Sunday 10/22/2023 13:01 Sunday -3 1.6 12101170 83238164.516 00012101170 3 10/22/2023 9:38 Sunday 10/22/2023 13:03 Sunday -3 1.6 12101170 83238170.516 00012101170 3 10/22/2023 9:38 Sunday 10/22/2023 13:03 Sunday -3 1.6 12101170 83238167.516 00012101170 3 10/22/2023 9:38 Sunday 10/22/2023 16:06 Sunday -3 1.6 12101170 83238244.516 00012101170 3 10/22/2023 9:38 Sunday 10/22/2023 13:01 Sunday -3 1.6 12101170 83238249.516 00012101170 3 10/22/2023 9:38 Sunday 10/22/2023 13:03 Sunday -3 1.6 12101170 83238246.516 00012101170 3 10/22/2023 9:38 Sunday 10/22/2023 16:06 Sunday -3 1.6 12101170 83238318.516 00012101170 3 10/22/2023 9:39 Sunday 10/22/2023 13:03 Sunday -3 1.6 12101170 83238315.516 00012101170 3 10/22/2023 9:39 Sunday 10/22/2023 16:06 Sunday -3 1.6 12101170 83263216.516 00012101170 6 10/22/2023 11:14 Sunday 10/22/2023 13:02 Sunday -6 1.6 12101170 83395974.516 00012101170 3 10/22/2023 16:35 Sunday 10/22/2023 16:06 Sunday -3 -63.36 *only events at 1:00 p.m. earlier on this day should be included in the sum (rows 60-87 excluding rows 69,73,77,81,84,86, each of which are captured in later sums)* 12101170 83395975.516 00012101170 3 10/22/2023 16:35 Sunday 10/22/2023 16:06 Sunday -3 -63.36 12101170 83395972.516 00012101170 3 10/22/2023 16:35 Sunday 10/22/2023 16:26 Sunday 2.55 -63.36 12101170 83395973.516 00012101170 3 10/22/2023 16:35 Sunday 10/22/2023 16:26 Sunday 2.55 -63.36 12101170 83398125.516 00012101170 3 10/22/2023 16:44 Sunday 10/22/2023 16:06 Sunday -3 -63.36 12101170 83398126.516 00012101170 3 10/22/2023 16:44 Sunday 10/22/2023 16:06 Sunday -3 -63.36 12101170 83398127.516 00012101170 3 10/22/2023 16:44 Sunday 10/22/2023 16:26 Sunday 2.64 -63.36 12101170 83398128.516 00012101170 3 10/22/2023 16:44 Sunday 10/22/2023 16:26 Sunday 2.82 -63.36 12101170 83403470.516 00012101170 3 10/22/2023 17:13 Sunday 10/22/2023 16:06 Sunday -3 -63.36 12101170 83403472.516 00012101170 3 10/22/2023 17:13 Sunday 10/22/2023 16:26 Sunday -3 -63.36 12101170 83408418.516 00012101170 3 10/22/2023 17:44 Sunday 10/22/2023 16:26 Sunday -3 -63.36 12101170 83417137.516 00012101170 3 10/22/2023 18:35 Sunday 10/22/2023 16:06 Sunday -3 -63.36 12101170 83417136.516 00012101170 3 10/22/2023 18:35 Sunday 10/22/2023 16:06 Sunday 1.71 -63.36 12101170 83417135.516 00012101170 3 10/22/2023 18:35 Sunday 10/22/2023 16:26 Sunday -3 -63.36 12101170 83417134.516 00012101170 3 10/22/2023 18:35 Sunday 10/22/2023 16:26 Sunday 1.89 -63.36 12101170 83417192.516 00012101170 3 10/22/2023 18:35 Sunday 10/22/2023 16:26 Sunday -3 -63.36 12101170 83540873.516 00012101170 3 10/23/2023 19:09 Monday 10/23/2023 20:16 Monday -3 -24 *sum of rows 69,73,77,81,84,86 - the most recent outcomes (bets placed on Sunday ~4:00 p.m. games)* 12101170 83575341.516 00012101170 3 10/23/2023 21:21 Monday 10/23/2023 20:16 Monday -3 -24 12101170 83576079.516 00012101170 3 10/23/2023 21:28 Monday 10/23/2023 20:16 Monday -3 -24 proc print; run; ... View more

**ADDENDUM to original post: I realized that this issue was being caused by starting with a "RETAIN" statement, which I use to put the variables in the desired order. But I'd still like to leave this question up because I'd appreciate any feedback on: How does a RETAIN statement work? When does it affect the outputs of a command in a DATA step? Does anyone have alternate/preferred strategies for reordering the variables in a dataset? Thanks! *********************************************************************** Original post:   Hello SAS community,   I'm very confused about how SAS deciphers "IF" Statements in the DATA step.   In this specific case, I'm working with an account dataset that has some conflicting information about when accounts close, and I am constructing an "effective" close date. Earlier in my data step, I used some IF statements to construct my desired close date. The last step is to convert that numeric close date to a string variable in the format YYYYMM.   Here's what I tried:   DATA WORK.dates_test; SET WORK.raw_dates; close_eff_n = acct_close_dte_n; IF closed = 1 AND acct_close_dte_n = . THEN DO; close_eff_n = maxdate_n; END; *(omitting some additional logic used here for parsimony); IF close_eff_n > 0 THEN DO; close_dte_eff = put(close_eff_n,yymmn.); END; RUN;   I had earlier written this last segment as:   close_dte_eff = put(close_eff_n,yymmn.); but this populated the string variable close_dte_eff with a value of "." when close_eff_n was missing, which is why I'm now trying to implement this conditional logic.   The problem is: where this condition fails, SAS populates the close_dte_eff field with whatever the last non-failed value was, which is completely incorrect. e.g. I have: close_eff_n 01MAR2023 01APR2023 . . 01JUL2021   I want: close_eff_n close_dte_eff 01MAR2023 202303 01APR2023 202304 .   .   01JUL2021 202107   But instead I get: close_eff_n close_dte_eff 01MAR2023 202303 01APR2023 202304 . 202304 . 202304 01JUL2021 202107   When I tried to replicate this problem with a simplified dataset, i.e. just taking the final input variables and creating the desired output, I got the result I want, so I suspect it might have something to do with the preceding IF-statements.   I can think of plenty of workarounds to get this to work as intended, so my question is not so much how to fix this, but why is this happening?   There's something fundamental about how the "IF-statement" is being processed where rows that fail the "IF" condition are being populated with the value of the last row that met that condition, and I would like to understand when SAS applies this behavior and when it does not. I can see this being a useful feature in some limited cases, but it's generally not what I would want to do when applying conditional logic.   I had thought that these sort of situations where SAS operates on one row depending on what was in the previous row only happen when there is a "BY" statement, but obviously that's incorrect as there is no "BY" statement in this DATA step.   I'd really appreciate some explanation as to when actions are applied to rows that do not meet the specified condition in an "IF" statement, and how to control that behavior, so I can make sure that the commands I write are applying to the rows that I expect them to apply to.   Please let me know if I can provide any other context or information that would be helpful.   Many thanks, Scott ... View more