Hi SAS Users,   I would like your help with the following.    Raw data: data have; input ID ; cards; 11 11 11 11 11 12 12 12 12 12 ; run;   I would like to assign ranks 1 - 5 to the above rows. Each ID should have all the ranks but randomly assigned.      Any help is appreciated.  Thank you for your time.  ... View more

Hi Community, I am trying to find a way to solve an issues below mentioned.    Raw data: ID Month Cost 1 Jan-07 5 1 Jan-07 5 1 Mar-07 5 1 May-07 5 2 Aug-08 6 2 Aug-08 6 2 Dec-08 13   Logic: The requirement has 2 tasks 1) Get the Minimum and Maximum of Month variable by ID. For Ex: for ID 1 Min is Jan-07 and Max is May-07. 2) Create a sequence of month-year from Minimum to Maximum. For Ex: for ID 1 The output starts at Jan-07 and ends at May-07, Even if raw data doesn't include Feb-07,April-07 the final output needs all the missing months to be added in sequence.   3) Sum the costs by ID and month and If no costs present then assign 0.  For Ex:  For ID 1 the Jan-07 costs add up to 10 and the output has 10 under the cost variables. and if not costs present to add then 0 is expected under the cells.     Output: ID  Month Cost 1 Jan-07 10 1 Feb-07 0 1 Mar-07 5 1 Apr-07 0 1 May-07 5 2 Aug-08 12 2 Sep-08 0 2 Oct-08 0 2 Nov-08 0 2 Dec-08 13   I am trying to solve this in Proc Sql but unable to get it work.    Any suggestions.    Thank you for your time.  ... View more

I am currently using a code to break down one data-set multiple ones based on a Month variable.   Data _null_; set work.unique_month; call execute('data ' !! Compress(Month) !! ';set work.monthly_flags; where Month = " ' !! Month !! ' "; run;' run; I would like to out put all the outputs to a permanent library. But the _null_ being the important aspect of the above code is writing the data to work library. Is there a way to write the datasets to a different library?? ... View more

Hi Community,   Thank you for your time. I am stuck with a situation where I have to flatten a dataset with suffix from different variable.   Here is the raw dataset.  id Intake ctx sz pel 2211 1 0 0 0 2211 4 0 0 1 2211 5 0 0 0 3322 1 0 0 0 3322 4 0 0 0 3322 5 0 0 0 4422 1 0 0 0 5533 1 0 0 1 5533 4 0 0 0 5533 5 0 0 0   The idea is to use the intake variable and add it as a suffix and flatten the dataset to get one row per patient final file. Expected Output.  id   ctx1 ctx4 ctx5 sz1 sz4 sz5 pel1 pel4 pel5 2211   0 0 0 0 0 0 0 1 0 3322   0 0 0 0 0 0 0 0 0 4422   0 . . 0 . . 0 . . 5533   0 0 0 0 0 0 1 0 0   I tried to use proc transpose by ID but had not luck.   Any help is appreciated. Thank you for looking.  ... View more

Hi SAS community,   I would appreciate your help in below problem:   I have a scenario in which a data-set sorted by ID and startdate and Enddate.   Each ID has multiple rows with startdate and enddate. If any 3 consecutive rows(Bunch of 3) have a date difference of <=60 months (1800 days) then Output.   For Ex: If the nth row of ID #1213 has an enddate- 01/12/2018 and nth-3 row has startdate- 31/12/2019 then that row should be flagged. Here is a sample dataset and expected output.   Row 3 startdate was compared to row 1 enddate and difference is 478 days. <1800 days or 60 months  Row 4 startdate was compared to row 2 enddate and difference is -30 days. <1800 days or 60 months  Row 5 startdate was compared to row 3 enddate and difference is 200 days. <1800 days or 60 months  Row 6 startdate was compared to row 4 enddate and difference is 1965 days. > 1800 days or 60 months So this should not be in the output.    Basically every startdate should be matched with n-3 end date and flagged for output. I tried to use lag function but it seems to only look for n-1. Any help is appreciated. Thank you for your time and effort.    ID Startdate Enddate Diff N and N -3 1122 1/12/2017 1/30/2017   1122 4/3/2018 7/22/2018   1122 5/23/2018 6/2/2018 478 1122 6/22/2018 7/7/2018 -30 1122 12/19/2018 12/31/2018 200 1122 11/23/2023 12/9/2023 1965 1122 3/22/2024 4/13/2024 1908                 Output       1122 43243 43253 478 1122 43273 43288 -30 1122 43453 43465 200 ... View more

Hi Community,   I need your help with the below problem.   If the per ID rows count is <6 then I would like to add the additional missing rows with the Score = 0;   data Dsn; input ID score; cards; 1 48 1 45 1 50 1 42 1 41 2 51 2 52 2 43 2 52 ; run;   Expected Output:   ID Score 1 48 1 45 1 50 1 42 1 41 1 0 2 51 2 52 2 43 2 52 2 0 2 0   Any help is appreciated.    Thank you for your time.    Best, SC.    ... View more