Hello all,   I have a large dataset in which I would like to change the label names of multiple columns to match the variable names. For example, I have 5 variables named new_diag_1, new_diag_2, new_diag_3, new_diag_4, new_diag_5. When I run a proc contents I see that the labels for each of these 5 variables is just new_diag. I would like the label to be the same as the variable name for each new_diag_1-5 variable. Note: In my actual dataset I have about 10-20 more variables that I will need to change the labels to match the variable names (some of the var names will have the same prefixes, e.g., new_treatment_1-5, new_dr_1-5, etc.)   I know I can rename each label by writing it out like the code below but I'm wondering if there's an easier way to change the name for multiple labels with the same prefixes. Any help is appreciated! proc sql; create table want as select new_diag_1 label = 'new_diag_1' from have; run; quit; ... View more

Hello all,   I have a long dataset and am using proc summary and sql to transform it to a wide dataset. I'm having trouble adapting the following code to retain all of my variables in my long dataset in the new wide dataset. Example of my code is below. For my age and count variables, I would like to retain them but if I include them in the IDGROUP statement, then SAS creates multiple age and count columns in my wide dataset (I would just like one, see desired output below).   data have; input id visit_num diag$ age treatment count; cards; 01 1 diabetes 42 1 1 02 1 stroke 78 1 2 02 2 cancer . 2 2 02 3 stroke . . 2 03 1 stroke 66 2 2 03 2 copd . 1 2 03 3 . . . 2 ; run; proc sql noprint; select max(n) into :n from (select count(*) as n from have group by id); quit; proc summary data=have; by id; output out=want idgroup(out[&n] (visit_num diag treatment)=); run; Desired output:(Note, in my actual dataset I have ~40 variables similar to count and age, where I would like just one column for each of these variables in my wide dataset). ID Visit_Num1 Visit_Num2 Visit_Num3 Age Treatment1 Treatment2 Treatment3 Count 01 diabetes     42 1     1 02 stroke cancer stroke 78 1 2   2 03 stroke copd   66 2 1   2 ... View more

Hello everyone,   I am trying to create a new binary variable based on the conditions of multiple other variables, see below for example dataset. Var1-3 values can be 0-3, -99, or missing  and these are 3 non-overlapping variables (i.e. if there is a value for Var1, there will not be a value for Var2-3). I'd like my NEW_VAR to code for the presence of a "legit" value (which I am defining as either a 1-3 in any Var1-3.). basically if any of the values for Var1-3 = 0 or -99 then NEW_VAR=0, else NEW_VAR=1. The problem with the code I below is that everything gets coded as 0 in the NEW_VAR. Any help is much appreciated.   Attempted code: data want; set have; if (var1>=1 or var2>=1 or var3>=1) then new_var=1; if (var1<=0 or var2<=0 or var3<=0)then new_var=0; run; Desired output: ID Var1 Var2 Var3 NEW_VAR 1 3 . . 1 2 . . 0 0 3 . -99 . 0 4 . . 2 1 ... View more

Hello all,   I am trying to count the number of responses across multiple variables. I would like to be able to output a new variable that counts the number of unique (i.e., the same value in one row should not be counted twice) values across diag_1 to diag_5 excluding any 'stress' or 'infection' values. Below is the code I have tried thus far based on another solution on this forum, I'm not sure how to finish adapting the code to give me the desired output.   data have; input id diag_1 :$9. diag_2 :$9. diag_3 :$9. diag_4 :$9. diag_5 :$9. age; cards; 01 diabetes stress . . . 42 02 stroke cancer stroke infection death 78 03 stroke copd . copd . 66 ; run; data want; set have; array diag {5} diag_1-diag_5; array new {5} $9 _temporary_; do _n_=1 to 5; new{_n_} = diag{_n_}; end; call sortc(of new{*}); count = (new{1} > ' '); do _n_=2 to 5; if new{_n_} ne new{_n_-1} then count + 1; end; run; Output of the above code: id diag_1 diag_2 diag_3 diag_4 diag_5 age treatment family Count 1 diabetes stress       42 1 2 2 2 stroke cancer stroke infection death 78 1 2 4 3 stroke copd   copd   66 2 1 2   Desired output: id diag_1 diag_2 diag_3 diag_4 diag_5 age treatment family Count 1 diabetes stress       42 1 2 1 2 stroke cancer stroke infection death 78 1 2 3 3 stroke copd   copd   66 2 1 2 ... View more

Hello all, I am trying to use proc transpose to change my dataset from long to wide. Below is an example of my data and my desired output, as well as the code I have attempted so far. My issue is that when I run my code, the visit_num columns of the transposed dataset do not contain the diag values. I'm wondering how to fix my code so I can get my desired output (and how to keep the Age variable from the original dataset). data have; input id visit_num diag$ age treatment; cards; 01 1 diabetes 42 1 02 1 stroke 78 1 02 2 cancer . 2 02 3 stroke . . 03 1 stroke 66 2 03 2 copd . 1 03 3 . . . ; run; proc transpose data=have out=wide2 prefix=visit_num; by id; var visit_num; run; Desired output: ID Visit_Num1 Visit_Num2 Visit_Num3 Age Treatment1 Treatment2 Treatment3 01 diabetes     42 1     02 stroke cancer stroke 78 1 2   03 stroke copd   66 2 1   ... View more

Hello all,   I have an original dataset which had 5 variables (alcohol_br1-alcohol_br5) which I have transposed the responses across all 5 variables into one column (COL1) in a new dataset. I ran a proc freq on this new column just to see what the values were in this column and I noticed that the frequency output will sometimes give me the same value multiple times but with different frequencies. I did a cross tab of the COL1 values by alcohol_br1-alcohol_br5 and the output below is an example of the output I get in SAS. I'm wondering if there's a reason why SAS would give the same value twice but with different frequencies? This occurs multiple times and I can't find any difference in the actual value in my dataset.     Molson Bombay Stella artois Molson alcohol_br1 2 7 65 599 alcohol_br2 1 0 23 4 alcohol_br3 0 0 90 0 alcohol_br4 0 3 2 2 alcohol_br5 0 6 0 1 ... View more