Hi,   I have 100 large datasets (a,b,c,d,e...) and 50 variables that I need to exclude from those datasets in a set statement.   How can I set up a Macro to efficiently exclude these from each dataset before creating the final dataset 'want'?   variables to exclude: d1, d2, d3, f1, f4, g7, h9...etc   data want; set a b c d e ... run;   ... View more

Hi,   I have a dataset like shown below with ID, month and signal.   I need to create new flags signal_2 and signal_3 that effectively retains the value of 2 for 2 months (signal_2) or 3 months (signal_3), otherwise keeps the value in signal.   What is the most efficient way for me to code this by ID and month?   id month signal signal_2 signal_3 1 Jan-20 0 0 0 1 Feb-20 0 0 0 1 Mar-20 1 1 1 1 Apr-20 1 1 1 1 May-20 2 2 2 1 Jun-20 2 2 2 1 Jul-20 3 2 2 1 Aug-20 3 2 2 1 Sep-20 3 3 2 1 Oct-20 3 3 3 1 Nov-20 3 3 3 1 Dec-20 4 4 4 2 Jun-20 0 0 0 2 Jul-20 4 4 4 2 Aug-20 4 4 4 2 Sep-20 4 4 4 2 Oct-20 2 2 2 2 Nov-20 0 2 2 2 Dec-20 1 2 2 2 Jan-21 1 1 2 2 Feb-21 2 2 2 2 Mar-21 4 2 2 2 Apr-21 4 2 2 2 May-21 4 4 2 2 Jun-21 1 1 1 ... View more

Hi all,   I have a large dataset with ID, month and 1p_flag (1 or 0) as shown below.   I am trying to create a flag by ID (variable '1_to_0'_to_1), so that for each month I can see if the flag has moved from 1 to 0 to 1 looking at historical months.   I then need to work out the months in between 1 to 0 to 1.   How can I best code this?   I hope this example shows where I want to get to.   Thanks in advance.   ID Month 1p_flag 1_to_0_to_1 months_between 1 Mar-12 0     1 Apr-12 0     1 May-12 1     1 Jun-12 1     1 Jul-12 0     1 Aug-12 0     1 Sep-12 1 1 3 1 Oct-12 0     2 Apr-12 1     2 May-12 0     2 Jun-12 0     2 Jul-12 1 1 3 2 Aug-12 0     2 Sep-12 0     2 Oct-12 0     2 Nov-12 1 1 4 2 Dec-12 0     2 Jan-13 1 1 2 2 Feb-13 1     ... View more

Hi all,   I have a large dataset with ID, month and flag (1 or 0) as shown below.   I am trying to create a count by ID (variable '1_to_0'), so that for each month I can see if the flag has moved from 1 (previous month) to 0 (current month).   Then I need to sum all instances for that ID and create a separate variable ('1_to_0_sum').   What is the most efficient way of writing this code?   Thanks in advance.   ID Month flag 1_to_0 1_to_0_sum 1 Mar-12 0   2 1 Apr-12 0   2 1 May-12 1   2 1 Jun-12 1   2 1 Jul-12 0 1 2 1 Aug-12 0   2 1 Sep-12 1   2 1 Oct-12 0 1 2 2 Apr-12 1   3 2 May-12 0 1 3 2 Jun-12 0   3 2 Jul-12 1   3 2 Aug-12 0 1 3 2 Sep-12 0   3 2 Oct-12 1   3 2 Nov-12 1   3 2 Dec-12 0 1 3 2 Jan-13 0   3 2 Feb-13 1   3 ... View more

Hi,   I have a dataset that needs to be modified to show trends over time.   I think I need to transpose but I'm unsure on the best way to code it.   Have: ID Date Date1 FB B 1 30/09/2018 30/06/2019 2.34 2.47 1 30/09/2018 31/03/2019 2.34 2.48 1 30/09/2018 31/12/2018 2.34 2.49 1 30/09/2018 30/09/2018 2.34 2.50 2 31/03/2019 30/06/2019 2.82 2.74 2 31/03/2019 31/03/2019 2.82 2.75 3 30/06/2018 31/03/2019 3.14 2.69 3 30/06/2018 30/06/2018 3.14 2.70 3 30/06/2018 30/06/2019 3.14 2.71 3 30/06/2018 31/12/2018 3.14 2.72 3 30/06/2018 30/09/2018 3.14 2.73   Need: ID q0 q1 q2 q3 q4 q5 1 2.34 2.48 2.49 2.5     2 2.82 2.75         3 3.14 2.7 2.71 2.72 2.73     q0 always takes the first row of the FB field q1-qx always takes the second row of the B field onwards (transposed)   Can you please suggest ways to code this?   Thanks in advance ... View more

Hi,   I have a dataset with multiple ID's that I want to reduce to unique ID's through their respective weightings.   For example, the dataset below shows ID, some additional variables required (Var1, Var2) and the weighting (wt). id var1 var2 wt AR_Q0 AR_Q1 AR_Q2 AR_Q3 AR_Q4 AR_Q5 1 xx 345 0.5 10 11 12 13 14 15 1 xx 345 0.3 50 51 52 53 54 55 1 xx 345 0.2 20 21 22 23 24 25 2 df 321 0.7 30 31 32 33 34 35 2 df 321 0.1 40 41 42 43 44 45 2 df 321 0.2 50 51 52 53 54 55   I need to create multiple each AR_Qx value by the weighting and sum up the rows by ID so it looks like this. For example AR_Q5 is calculated as (15*0.5)+(55*0.3)+(25*0.2)=29   id var1 var2 AR_Q0 AR_Q1 AR_Q2 AR_Q3 AR_Q4 AR_Q5 1 xx 345 24 25 26 27 28 29 2 df 321 35 36 37 38 39 40   What is the most efficient way of performing this calculation. At the moment I am doing this in Excel.   Note: Unfortunately I am unable to modify the source dataset as it is part of another process.   Thanks in advance. ... View more