Thanks PaigeMiller. The variables need to be dropped within the set statement as they are causing issues when trying to set together. Your second macro code is what I was after.
... View more
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 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 SAS Community. I have a dataset with three variables and I am trying to create the fourth one 'need' 'Need' is months since Flag is last>0 resetting to 1 once flag>0. I hope I explained this correctly, but essentially you can see how the variable should be created from the datastep below. The dataset is already sorted by ID and Date. How can I code this efficiently? Datastep is below: data have; input ID Mon $ Flag Need; datalines; 1 Jan-06 0 0 1 Feb-06 0 0 1 Mar-06 0 0 1 Apr-06 0 0 1 May-06 0 0 1 Jun-06 0 0 1 Jul-06 0.7 1 1 Aug-06 0 2 1 Sep-06 1 1 1 Oct-06 1.3 1 1 Nov-06 0 2 1 Dec-06 0 3 2 Feb-14 0 0 2 Mar-14 0 0 2 Apr-14 0 0 2 May-14 0 0 2 Jun-14 0.6 1 2 Jul-14 0 2 2 Aug-14 0 3 2 Sep-14 0 4 2 Oct-14 0 5 2 Nov-14 2 1 3 Aug-18 0 0 3 Sep-18 0 0 3 Oct-18 0 0 3 Nov-18 0 0 3 Dec-18 0 0 ; run;
... View more
Thanks @PaigeMiller the car_flag3 variable works perfectly! MSC should continue counting as it's a months since car>0. For example, if car>0 in Feb-14 for a particular ID then MSC=1 in Mar-14 MSC=2 if car=0 Apr-14 MSC=3 if car=0 In May-14 if car>0 again for that ID then the MSC counter starts from 1 again. Can that be incorporated into your code?
... View more
sorry @ballardw I meant: I want to create the following two new variables: Car_flag3: Where car>0 in the last 3 months then car_flag=1; else car_flag=0 MSC: Counter for number of months since car>1 The three months should all have to be the same ID
... View more
Sorry @PaigeMiller I meant: I want to create the following two new variables: Car_flag3: Where car>0 in the last 3 months then car_flag=1; else car_flag=0 MSC: Counter for number of months since car>0
... View more
Thanks Reeza. For the first part, I tried : INCK('day',pi.DateStart,ld.TDate)>=0 How would I code the second part which tried to use tdate when DateEnd is not populated.
... View more
Hi, How do I convert the following code extract from SQL to SAS? I don't believe datediff works in SAS. LEFT JOIN pi_table as pi ON lo.id=pi.id AND DATEDIFF(dd,pi.DateStart,ld.TDate)>=0 AND DATEDIFF(dd,ld.TDate,ISNULL(pi.DateEnd,ld.TDate))>=0 Thanks
... 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
This site uses cookies and related technologies for site operation, analytics and third-party advertising purposes, as described in our SAS Privacy Statement. You may consent to our use of these technologies, reject non-essential technologies or further manage your preferences. To opt out of SAS making information relating to cookies and similar technologies available to third parties for advertising purposes, select "Required only." To exercise other rights you may have related to cookies, select "Manage cookies."
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
