I am trying to merge two data sets in what I think is a one to many merge. It's a bit complex but I am trying to keep as much of Have1 because I have other calculations that depend on that dataset format. My goal is to get Height1 Weight1 Head1 Feet1 BMI in the long format.        data have1; input Patient $ Variable $ Value Visit $; cards; 1 Height 1 a 1 Weight 2 a 1 Head 1 a 1 Height 3 b 1 Weight 4 b 1 Head 5 b 1 Feet 6 b 2 Height 3 c 2 Weight 2 c 2 Head 6 c ;<code></code><code></code>       data have2; input Patient $ Visit $ Height1 Weight1 Head1 Feet1 BMI; datalines; 1 a 1 2 1 . 5 1 b 3 4 5 6 5 2 c 3 2 6 . 4 ;   data want; input Patient $ Variable $ Value Visit $ Height1 Weight1 Head1 Feet1 BMI; cards; 1 Height 1 a 1 . . . . 1 Weight 2 a . 2 . . . 1 Head 1 a . . 1 . . 1 BMI . a . . . . 5 1 Height 3 b 3 . . . . 1 Weight 4 b . 4 . . . 1 Head 5 b . . 5 . . 1 Feet 6 b . . . 6 . 1 BMI . b . . . . 5 2 Height 3 c 3 . . . . 2 Weight 2 c . 2 . . . 2 Head 6 c . . . 6 . 2 BMI . c . . . . 4   ; In theory my attempt would be the following but I'm not sure how to get the BMI added to the Variable list:   data want; merge have1 have2; by Patient Visit; run;       Any insight would help! Thanks! ... View more

I have the following data set that I would like to melt and reshape by aggregating 1) the total number of days on drugs where it was taken >=7 consecutive days 2) the drug amount they took.   Source data: Patient Drug StartDate EndDate amountperday unit A E 2016-02-20 2016-02-28 1 mg A G 2016-03-20 2016-03-27 2 mg A F 2016-02-20 2016-02-21 7 mg A E 2016-02-20 4 mg B E 2016-01-05 2016-01-24 3 mg B G 2016-01-07 2016-01-14 5 mg Result: Patient Drug totamount unit Days StartDate EndDate A EG 22 mg 15 2016-02-20 2016-03-27 B EG 95 mg 27 2016-01-05 2016-01-24 For A the 22 was calculated 1(8days) + 2(7days). For A the 15 was calculated as 8 days + 7 days. For B the 95 was calculated as 3(20days) + 5(7days). For B the 27 was calculated as 20 days + 14 days - 7 overlapping days.   My attempt at this would be the following:    data calculation;  set mydata;  days=datdif(startdate, enddate, 'act/act'); [I would have to mark the overlapping days but I'm not sure how I would do that and then add a by statement] if days >=7 then dose = amountperday*days; else = . ;  run;    Please let me know how I can get my desired outcome. It does not have to use my attempt.        ... View more

I have a two data sets that look like the following simplified:  Dataset 1                                                                                             Dataset2 Patient  Drug      StartDate        EndDate       etc                                 Drug  etc  A             E       2016-02-20     2016-02-21      -                                     E        -  A             F       2016-01-21     2016-01-25      -                                     G        -  A             G      2016-03-20     2016-03-21       - B             E      2016-01-05     2016-01-24       - B             G      2016-01-07     2016-01-14       - C             H      2016-02-20     2016-02-28       -    I am trying to keep only the observations where the Drug lists match:   Patient  Drug      StartDate        EndDate       etc                                  A             E       2016-02-20     2016-02-21      -                                                                  A             G      2016-03-20     2016-03-21       - B             E      2016-01-05     2016-01-24       - B             G      2016-01-07     2016-01-14       -   I've tried the following but I'm not sure how to read from the drug list in the second data set:  data remove; set mydata; if Drug = [matching drugs from drug list] then delete; ; Thanks for any help!   ... View more

I have a dataset that looks like the following for many patients        Patient      VisitDate     Value     Unit     Type       A            Jan12019        1           m       Height       A            Jan12019       50         kg       Weight       A            Jan52019        2           m       Height       A            Jan52019       55         kg       Weight I am trying to add BMI to get the following dataset for those patients:       Patient     VisitDate      Value     Unit      Type      A            Jan12019         1          m       Height      A           Jan12019         50         kg      Weight      A            Jan52019         2          m       Height      A           Jan52019         55         kg      Weight      A           Jan12019     50/1^2    kg/m2     BMI      A           Jan52019     55/2^2    kg/m2     BMI   I am trying to understand the logic beyond programming this in SAS. Below is what I have so far in psuedo code: Create BMI data set. For each patient on each visit date, value = weight/height^2. Type = BMI. Unit = kg/m2. Keep Patient, VisitDate info. ... View more

I have a data set that looks like the following:  Column 1 Column 2 Column 3 Data 4 3 Data 1 3 2 Data 2 3 4 I would like to insert a row under the title names that duplicates  row Data 2 renamed.  Column 1 Column 2 Column 3 New 3 4 Data 4 3 Data 1 3 2 Data 2 3 4 How can I do so? I've seen this used: proc sql; create table totals as  but I'm just looking for the easiest way.  ... View more

I am trying to join the following two data sets:  data testA; input categorical $3. value; *order = _n_; datalines; Dog. M 7 F 5 Cat. M 4 F 2 ; run; data testA; set testA; order=_n_; run; data testB; input categorical $2. value; datalines; Dog. F 3 Cat. M 1 F 2 ; run; proc sql; create table final as select a.*,b.* from testA a left join testB b on a.categorical=b.categorical order by order; quit; My desire output is the following:  data testA; input categorical $ value value2; datalines; Dog . . M 7 . F 5 3 Cat . . M 4 1 F 2 2 ; run;  The problems I've encountered is that 1) the 'categorical' id is not sorted alphabetically and I do not want to change its order 2) Since there are two Ms and Fs I don't know how to join without renaming the M F so that it's unique 3) It could be an inner join since what may be in value may not be in value2 ... View more