I have two datasets as below: data2016: item quality Jun Jul Aug apple bad 1 1 3 apple good 6 8 9 orange bad 1 2 1 orange good 4 9 6 peach bad 2 2 3 peach good 10 12 17 data2017: item quality Jun Jul Aug apple bad 2 2 3 apple good 13 14 15 orange bad 3 5 1 orange good 8 7 13 cherry bad 1 4 2 cherry good 7 9 5   Now I need to make a summary result as below: summary: item quality Jun Jul Aug item quality Jun Jul Aug apple bad 3 3 6 apple good 19 22 24 orange bad 4 7 2 orange good 12 16 19 cherry bad 1 4 2 cherry good 7 9 5    peach bad            2            2            3 peach good 10 12 17   Logic is that sum the data of 2016 and 2017 result for each item, quality and month. I tried use cumulative sum and remove some rows but do not know how to calculate cumulative sum for several month like Jun Jul Aug, or is there any other easier way of doing this?   My cumulative sum code: proc datasets; append base=data2016 data=data2017; run;      proc sort data=data2016; by item quality; run;   data summary; set data2016; by item quality; if first.flag then June=Jun July=Jul August=Aug; else June=Jun+June July=Jul+July August=Aug+August; run;   then I will drop and rename columns and join summary having Aug=max(Aug), haven't done this step because the sum is still not working.   Please help to see how make the sum step working, and is there any other easier way to do this?   Thanks,   ... View more

I want to update value of one column in an existing table by some condition. Please help, table 'test' looks like below: ID    Group1    Value1    Group2    Value2    Comment 01     aaa            1            bbb           2 02     aaa            2            bbb           1 03     aaa            3            bbb           4 04     cc              2             d              6 05     cc              1             d              4 06     cc              5             e              7   The idea is add value to Comment, compare Value1 or Value2 by corresponding group, the row with the lowest value will have 'Y' in comment, else get a 'N'. For example, row ID=01 has value1=1 which is lowest in Group1=aaa, then comment='Y'; row ID=02 has value1=2 not the lowest in aaa, but it has Value2=1, the lowest in Group2=bbb, so comment='Y'. The updated table 'want' would be like below: ID     Group1      Value1      Group2     Value2       Comment 01       aaa            1               bbb            2                 Y 02       aaa            2               bbb            1                 Y 03       aaa            3               bbb            4                 N 04       cc              2                 d              6                 N 05       cc              1                 d              4                 Y 06       cc              5                 e              7                 Y   How can I do it in SAS?   Thanks, Leon ... View more

I want to pull out data by SAS from database. Suppose I have a database with thousands of people's information. I made a filter to randomly select 2 people born from each month of certain year. dataset LIST looks like below: name       birthday       month green        xxxx               1 white         xxxx               2 brown       xxxx               3 black         xxxx               4 and so on Now I want to use the name as one criterion for another data pulling. My code will be: proc sql;      connect to xxxx(database);      create table want            as select * from connection to database              (select a.name, a.occupation, a.employer....              from surveydet.list              where (a.occupation  in xxxxxxxxxx)              and (a.employer in xxxxxxxxxxx)              AND (A.NAME= LIST.NAME); disconnect from databse; quit;   The code seems not work, please help to teach me how to pull data using names exactly pre-selected in dataset LIST.   Thanks, ... View more

I have a problem of merge two datasets,  data A year        row        col 2017         1            1 2017         1            2 2017         2            1 2017         2            2 2018         1            1 2018         1            2 2018         2            1 2018         2            2   data B row       col       x        y 1           1         a1      a2 1           2         b1      b2 2           1         c1      c2 2           2         d1      d2   Trying to combine the two datasets as below:   year        row        col          x           y 2017         1            1         a1         a2 2017         1            2         b1         b2 2017         2            1         c1         c2 2017         2            2         d1         d2 2018         1            1         a1         a2 2018         1            2         b1         b2 2018         2            1         c1         c2 2018         2            2         d1         d2     If I sort A by YEAR ROW, then merge A and B by ROW, I will only get merged data with 2017, log shows ERROR: BY variables are not properly sorted on dataset A.   If I sort A by ROW YEAR, then merge A and B by ROW, there will be no error, but for the year 2018, x and y values are not right.   Please help, thanks!   ... View more