About TheNovice

TheNovice · ‎10-09-2020

Hi there, hoping for a data step and proc sql solution please. C_Date and E_date represent same date, different formats. Same for Memo date and m_date my data has records where the memo_id is duplicated for 2 records because the M_date is equal to the next_date and the c_date.Unfortunately the time stamp in e_date is not specific. So I need the memo_id to only be available for the first/earliest record. Original: ID TIME C_DATE e_date next_date MEMO_TYPE memo_date m_date MEMO_ID 665 0 02-Sep-20 02SEP2020:00:00:00 03-Oct-20 MC 03-Oct-20 03OCT2020:12:56:02 4411047528 665 1 03-Oct-20 03OCT2020:00:00:00 08-Oct-20 MC 03-Oct-20 03OCT2020:12:56:02 4411047528 Outcome needed: ID TIME C_DATE e_date next_date MEMO_TYPE memo_date m_date MEMO_ID 665 0 02-Sep-20 02SEP2020:00:00:00 03-Oct-20 MC 03-Oct-20 03OCT2020:12:56:02 4411047528 665 1 03-Oct-20 03OCT2020:00:00:00 08-Oct-20 . . . . Appreciate any and all direction.

TheNovice · ‎09-15-2020

Thank you all so much. all the solutions work 🙂 I will tag the first one with the accept then. thanks again

TheNovice · ‎09-15-2020

Apologies, yes, the join key would be ban

TheNovice · ‎09-14-2020

Hi all, I have to solve this using proc sql because it will then be converted to a sql script for automation and needs to be as close as possible (just how things work here). My dataset has duplicate records like below with different dates. I need to join to a second table where b.date > a.date. But i only want to join to the closest match to b.date which is the max (a.date) Table a BAN col_date 123 20-Jun-20 123 21-Jul-20 Table b status status_date r 20-Aug-20 to get Table a BAN col_date status status_date 123 20-Jun-20 123 21-Jul-20 r 20-Aug-20 Would appreciate any and all guidance. thank you

TheNovice · ‎09-08-2020

Thank you so much for the explanation. this solution worked perfectly as well 🙂

TheNovice · ‎09-04-2020

It works.Thank you so much!

TheNovice · ‎09-04-2020

Thanks for the quick reply... i think i may have simplified it too much.. the resource_names can be different. I just need the dates to keep populating until the new dates starts input Cal Date Resource Name 01/02/2019 B . D . C 01/03/2019 E . E output needed Cal Date Resource Name 01/02/2019 B 01/02/2019 D 01/02/2019 C 01/03/2019 E 01/03/2019 E

TheNovice · ‎09-04-2020

I have received a dataset like below: Dataset is sorted by Cal_date but the rows are blank after the first row is populated. I am trying to populate the remaining rows with the previous date... until the next date starts... Cal Date Resource Name 01/02/2019 B . B . B 01/03/2019 B I put a variable flag = 0 where cal_date = . but i am stuck I am trying below but not working... any direction would be appreciated data want; set test; format newdate date9.; retain lag_date; lag_date = cal_date; if flag =1 then newdate = lag_date; drop lag_date; run;

TheNovice · ‎09-04-2020

I have been given a dataset where the data columns are all blank except for the first record like below: The dataset is huge so i need to do this efficiently. I need the output where the blank dates will populate with the first date until the next date starts and so on. The list is already sorted by the dates. I tried the following: data want; set test; format newdate date9.; retain lag_date; lag_date = cal_date; if flag =1 then newdate = lag_date; drop lag_date; run; appreciate any corrections Cal Date Resource Name 01/02/2019 B . B . B 01/03/2019 B to Cal Date Resource Name 01/02/2019 B 01/02/2019 B 01/02/2019 B 01/03/2019 B

TheNovice · ‎09-04-2020

Sorry for the delay all. I was on vacation and taking a break from all work. I tested the next 2 suggestions and they both work. Thanks again 🙂

TheNovice · ‎08-15-2020

It worked. Thank you so much. I was going to go the proc sql way by subsetting and then joining again but your method is fast and elegant. Thanks for the explanation. I would just like to compare this to the second solution posted and see if there is a variance in output. Your extract is what i was looking for. Your assumption is bang on.

TheNovice · ‎08-15-2020

Thank you Shmuel, But the output was blank... no errors. the second do block was not closed so i closed it like this: data want1; set have; by ID; retain lag_type lag_date; if first.ID then do; lag_type=" "; lag_date=.; end; else do; lag_type = type; lag_date=date; end; if not first.ID and type='Symend' and lag_type='Regular' then do; R_type = lag_type; R_date=lag_date; S_type = type; S_date = date; output; keep ID R_: S_: ;end; run; The solution by mkeintz worked. I would love to run both and see if there is a variance. Would appreciate you looking into it. Thank you again

TheNovice · ‎08-15-2020

A dataset so i can further analysis. so i would want all id's where there was an event R and then an event S after. I only care about extracting 2 rows per ID this way. R would be my champion and S would be the challenger

TheNovice · ‎08-15-2020

Hi all, The dataset is like below with accounts undergoing treatment R. At some point they will switch to treatment S. Once they switch to S they will not switch back to R. I would like to identify 2 consecutive records where an ID had R and then received S Note: Some id's will never move to S ID date type 99997887 05-May-20 R 99997887 04-Jun-20 R 99998960 02-Jun-20 S 99998960 03-Jul-20 S 99998960 03-Aug-20 S 100003216 14-Jun-20 R 100003216 15-Jul-20 R 100005355 05-May-20 R 100005355 04-Jun-20 R 100005355 05-Jul-20 R 100005981 03-Jun-20 R 100005981 03-Aug-20 S 100009933 10-May-20 R 100009933 10-Jul-20 R 100009933 09-Aug-20 R 100010577 22-May-20 R 100010577 22-Jul-20 S

TheNovice · ‎06-20-2020

Thanks Patrick, it did work. I missed the descending sort at first. Appreciate it.

Online Status	Offline
Date Last Visited	‎11-10-2022 09:08 PM

Re: Aggregating data by week

Re: Aggregating data by week

Re: Aggregating data by week

Re: Aggregating data by week

Re: Aggregating data by week

Aggregating data by week

Re: Assign value based on status

Re: Assign value based on status

Re: Assign value based on status

Re: Assign value based on status

Re: How to calculate days between 2 dates in separate rows and same gr...

Re: How to calculate days between 2 dates in separate rows and same gr...

Re: Converting Character to date in sas when the format is word date

Re: Assign value based on status

Re: Join to one of 4 tables based on a date condition

Re: Create a table of column totals and append daily information

MODIFYING row values based on duplicate

Re: Left join a date to row with max date where duplicate records exis...

Re: Left join a date to row with max date where duplicate records exis...

Left join a date to row with max date where duplicate records exist

Re: Populating blank cells with previous date

Re: Populate blank rows with previous date

Re: Populate blank rows with previous date

Populate blank rows with previous date

Populating blank cells with previous date

Re: Want to subset a champion/Challenger dataset from larger populatio...

Re: Want to subset a champion/Challenger dataset from larger populatio...

Re: Want to subset a champion/Challenger dataset from larger populatio...

Re: Want to subset a champion/Challenger dataset from larger populatio...

Want to subset a champion/Challenger dataset from larger population

Re: How to calculate days between 2 dates in separate rows and same gr...