Hi there,   I have a question and hopefully I will make sense.   I have two tables:   data have1; input ID $ DT :yymmdd10.; format DT yymmddd10.; datalines; A 2024-07-04 A 2024-07-25 ; run; data have2; input ID $ DT :yymmdd10. AMT; format DT yymmddd10.; datalines; A 2024-07-03 10 A 2024-07-04 20 A 2024-07-04 30 A 2024-07-11 40 A 2024-07-11 50 A 2024-07-16 70 A 2024-07-16 80 A 2024-07-25 90 A 2024-07-26 100 ; run; I want to combine them in such a way that in the first table I can add the total sum of the second table for before-or-equal and after the dates in the first table.   Essentially, I want the following: data want; input ID $ DT :yymmdd10. Before_upto_DT After_DT; format DT yymmddd10.; datalines; A 2024-07-04 60 430 A 2024-07-25 390 100 ; run; My attempt was to join them using PROC SQL but obviously this is not working.   Any help please.   Thanks in advance.     ... View more

Hello there,   I would like to count the number of non 0's and non missing values row-wise.   For example, I have this dataset:   data have; input name $ A B C D E F G H I J K L; cards; AAA 100 50 30 55 66 90 100 110 150 22 0 . BBB 200 70 30 90 21 80 200 321 456 67 . 0 CCC 300 40 60 40 43 30 300 455 210 57 304 310 ; run; and I want a column that shows the number of non 0s and non missing per each row:     data want; input name $ A B C D E F G H I J K L No_Of_Non0_or_Nonmissing; cards; AAA 100 50 30 55 66 90 100 110 150 22 0 . 10 BBB 200 70 30 90 21 80 200 321 456 67 . 0 10 CCC 300 40 60 40 43 30 300 455 210 57 304 310 12 ; run; I have created the following but it is not working as expected: data attempt; set have; array s {*} _numeric_; by name; do _i_=1 to dim(s); if s[_i_] not in (.,0) then do; if first.name then No_Of_Non0_or_Nonmissing+ 1; else No_Of_Non0_or_Nonmissing=0; end; end; run; As you may see it adds the non 0s and non missing values to the next row: Any help would be much appreciated.   Thanks.     ... View more

Hi there,   I have the below dataset:     data have; input amount; datalines; 10 11 11 33 456 54 66 66 89 ; run; I want to add a column showing how many rows in the dataset have greater or equal value than the current row:   data want; input amount No_times_gr_eq; datalines; 10 9 11 8 11 8 33 6 456 1 54 5 66 4 66 4 89 2 ; run; Any help would be much appreciated. Thanks. ... View more

Hi there,   I wondered if you could help me.   I have the below dataset:   data have; input id date :ddmmyy10.; format date ddmmyy10.; datalines; 1 20/11/2019 1 21/11/2019 1 22/11/2019 1 25/11/2019 1 27/11/2019 1 29/11/2019 2 20/11/2019 2 21/11/2019 2 23/11/2019 2 25/11/2019 2 26/11/2019 2 28/11/2019 3 29/11/2019 3 01/12/2019 ; run; I would like to add a column that represents the number of dates in a row. This is what I wanted:   data want; input id date :ddmmyy10. COLUMN_NEEDED; format date ddmmyy10.; datalines; 1 20/11/2019 1 1 21/11/2019 2 1 22/11/2019 3 1 25/11/2019 1 1 27/11/2019 1 1 29/11/2019 1 2 20/11/2019 1 2 21/11/2019 2 2 23/11/2019 1 2 25/11/2019 1 2 26/11/2019 2 2 28/11/2019 1 3 29/11/2019 1 3 01/12/2019 1 ; run; As long as there are dates in a row by each ID, the COLUMN_NEEDED adds 1.   Thanks in advance. ... View more

Hello,   I would ask for help on the below please.   I have a dataset in the following format:     data have; INFILE DATALINES DELIMITER=',' DSD; input Branch $ Employee Products $50.; datalines; A, 1, Ball, Paper, Pen, Glass, Car, B, 1, Dog, Cat, Bird, Water, Pencil, B, 2, Ball, Paper, Pen, Glass, Car, A, 2, Dog, Cat, Bird, Water, Pencil, run; The column "Products" contains values separated by commas.  I want to create a new row with each of these values.     The new structure should be as follows:   data want; input Branch $ Employee Products $50.; datalines; A 1 Ball A 1 Paper A 1 Pen A 1 Glass A 1 Car B 1 Dog B 1 Cat B 1 Bird B 1 Water B 1 Pencil A 2 Ball A 2 Paper A 2 Pen A 2 Glass A 2 Car B 2 Dog B 2 Cat B 2 Bird B 2 Water B 2 Pencil ; run; Any help please.   Thanks. ... View more