Hello: I have a table "have" which can be created by the code attached. I would like to reshape it to the table "want" (see eg in the code). Basicly, the table "have" contains the counts of types of variable1 and variable2. It can be thought as types of sales-person, non_sales-person, both types for var1. For var2, it could be locations of "a" or "b". I have tried proc transpose. But I failed to do so since not sure how to handle the varialbe names. data have; input year num_s_a num_s_b num_ns_a num_ns_b num_both_a num_both_b; cards; 2010 1 2 3 4 5 6 2011 7 8 9 10 11 12 ; run; data want; input year v1_type $ v2_type $ count; cards; 2010 s a 1 2010 s b 2 2010 ns a 3 2010 ns b 4 2010 both a 5 2010 both b 6 2011 s a 7 2011 s b 8 2011 ns a 9 2011 ns b 10 2011 both a 11 2011 both b 12 ; run; ... View more

Hello: I tried to combine multiple tables using loop, say i have 20 tables: t20, t19, t18, ......for each year. So far, I have tried join or union from proc sql, but don't know how to do it. Maybe data step, but don't know how. Here are the details about the tables: Each table has a common "id" field and two other variables. e.g. tables look like T20:                                     T19:                                         T18:                            T17 ................................... id        a20    b20                  id        a19    b19                     id        a18    b18         ........................................ id1       0        0                     id1       1        0                        id4      1          1            ....................................... id2       0        1                     id4       0        0                        id5      0          0  id3       1        1 I would like to combine them together to have first column as "id", it will contain all the ids appearing in all the tables. Then, all the "a" and "b" variables will be after the "id". If certain "id" were not in certain year, the result table will be a missing value. The result table look like: id        a20    b20    a19     b19     a18       b18 ....... id1      0         0        1         0         .            . id2      0         1         .         .          .            . id3      1         1         .         .          .            . id4      .          .         0         0         1           1 id5      .          .         .          .          0           0  I have put some code to create 3 tables as examples to start. Thanks for any help! data t20; input id $ a20 b20; cards; id1 0 0 id2 0 1 id3 1 1 ; run; data t19; input id $ a19 b19; cards; id1 1 0 id4 0 0 ; run; data t18; input id $ a18 b18; cards; id4 1 1 id5 0 0 ; run;       ... View more

Hello: I have a data set that has more than 300 variables. I would like to select some variables that have the same prefix and suffix. e.g I only want to keep variables with prefix "s" and suffix "2020". Please my code for short version of my data set. The code includes something I tried but not working. Thanks, data have; input var1 var2 s12020 t12020 u12020 s22020 t22020 u22020 s32020 t32020 u32020; cards; 1 2 3 4 5 6 7 8 9 1 2 ; run; /* I would like to keep s12020 s22020 s32020, this is an example, I have longer var list than this in real data */ /* data want; set have (keep= s12020-s32020); run; ERROR: Not all variables in the list s12020-s32020 were found. */ ... View more

Hello: I am trying to remove space inside strings, e.g. remove the space before "1" in the string "XXX-1234. 1". I have many values in the same format i.e. "XXX-####.#". Many of them have space after the "." and before the last digit. I would like to remove any space in the strings. Some of them can be removed successfully by using compress(). But some of them, the space cannot be removed by compress().   Not sure if the space was from some special char or something else than typing. How can I identify and remove them.   Any help? Thanks, ... View more