SAS Support Communities

Thanks for your help it worked! ... View more

Thanks for your reply @Tom Yes, there was an error in my value for the 'new_variable' in the last sample and also it doesn't make a difference if the values for new_variable is character or numeric. Let me try your code. An array could be a better solution than the macro. I will get back to the post in a bit. ... View more

Thanks for your reply @Patrick, however &og is not pa pa1 pa2 pa3 pa4 but the values in it which are many in the real dataset. Please review my reply to @novinosrin ... View more

@novinosrinThe macro was available and I wanted to make use of it.   This is what I am trying to accomplish. Here are the basic statements...   %let og=(5,6,7); %let tw=(9,3);   data want;   set have; if ( a in (&og.) or   pa1 in (&og.) or   pa2 in (&og.) or   pa3 in (&og.) or   pa4 in (&og.) ) and   ( lt=1 and lp in (&tw.) ) then new_variable =1;                                        else new_variable=0;  run;    I wanted to avoid using the multiple 'or' syntax hence the macro.   Please let me know if there is way to avoid that and/or make use of the macro. Also I had an error in the dataset 'want' and I have copy pasted both below. The 'have' dataset have is large and there are multiple records for each id.   have id pa pa1 pa2 pa3 pa4 lt lp 1 5 5 5 5   1 9 2 6 6 6     1 3 3 7 7 7 7 7 2 2   want id pa pa1 pa2 pa3 pa4 lt lp new_variable 1 5 5 5 5   1 9 1 2 6 6 6     1 3 1 3 7 7 7 7 7 2 2 0 ... View more

Hi @Reeza and @ballardw,  I provided the tables as examples. The full code is here:   proc sql;   create table table_c as     select *        from table_a c full join table_b t          on c.id=t.id and c.date=t.date;  quit;   I ran the above code and it gave me an error saying there are duplicates in 'id' column. I also realized joins do not overlay columns. I was wondering if there was way through joins to go about getting table_c with unique rows for values in code1 and code2. Hope this explains what I am trying to acheive. Thanks. ... View more

@ballardw Thanks much for the solution!  I realized you had provided it already and it was vey helpful. ... View more

Thanks @ballardw for the tips. Actually the array worked for me and I converted the character values to numeric after tweaking it. I gave a value of : '1' to the event1 value of interest (which is "in_target") ' 2' to those of event1 values of no interest and a period for missing and blank values.    I am now concerned about calculating the average proportion for each event and then its confidence interval.   @Reeza , I  am looking to use the proc freq with the binomial option. At present I have transposed my dataset and it looks like this: Value '1' for variable "Event" is the event value fo interest.   ID Period Event 1 period1_event1 1 1 period2_event1 . 1 period3_event1 . 1 period4_event1 . 2 period1_event1 2 2 period2_event1 1 2 period3_event1 . 2 period4_event1 . 3 period1_event1 1 3 period2_event1 1 3 period3_event1 . 3 period4_event1 .   Is this how I should be executing the proc freq with the binomial option?   proc freq data=have;      ods output Binomial=ci;      by id;      tables event / binomial; run; If 'yes' then I will be getting proportion and Upper Conf Limit and Lower Conf Limit by each id. Do I then extract all these estimates for each id from output dataset 'ci' and take its average for the population? Please advice. Also, another way I apporached this is to calculate the numerator,denominator and proportion for each distint id and then calculate the average proportion for the population.  This is how my output looks for the population for event 1: Table 1                 ID Period1 Period2 Period3 Period4 Numerator Denominator Proportion Calculation Average_proportion ID1 in_target Missing low in_target 2 3 =0.66 (0.66+0+0.75)/3=0.47 ID2 low low Missing missing 0 2 =0   ID3 low in_target in_target in_target 3 4 =0.75   Should I calculate the confidence intervals manually using P = PERCENT/100 ; N = COUNT + LAG(COUNT) ; LB = P - ( 1.96*SQRT( P*(1-P)/N ) ) ; * reset lower bound to 0 if <0 ; IF LB < 0 THEN LB = 0 ; UB = P + ( 1.96*SQRT( P*(1-P)/N ) ) ; * reset upper bound to 1 if >1 ; IF UB > 1 Then UB = 1 ;   What would be the most efficiat way to do this? Please reaply as soon as possible. Much thanks for your help. ... View more

Hi @ballardw. Thanks for your reply.Yes, I should have known to display my data in a form that can be tested. Also, I realized after that proc sql will require me to transpose the data as count will not be applicable to multiple columns.   I wanted to avoid that. If possible, I would like to use the sum function. I anyways have to convert the values of period1 to period5 to numeric. I also have blanks in the data that have to be converted into missing numeric values.  So on a clean slate here is my data and a data manipulation question as well. The values of interest to me are 'in_target'. The other values are 'low' and 'high' which are the 'event vaues not of interest'. 'missing' and blank values both need to be treated as 'missing'.   data have;    input id period1  period2  period3 period4 period5 datalines; 1 in_target 2  in_target  missing                    low         in_target 3  high         high          missing   missing  missing              4  low          in_target   in_target in_target in_target run;   I am looking to convert the values of in_target to 1 , low and high as '0' ,missing and blank with a 'period'.   I used the array array ch(*) $ period:; array nu(*) periodnew:; do i = 1 to dim(ch); if ch(i)='in_target' then do; nu(i)=1; end; else if ch(i)='low' then do; nu(i)=0; end; else if ch(i)='high' then do; nu(i)=0; end; else if ch(i)='missing' then do; nu(i)=.; end; else if ch(i)=' ' then do; nu(i)=.; end; end; run;   I am getting an error stating ERROR: Array subscript out of range at line # column #   Could you first please tell me what is wrong with my array code?       ... View more

Hi DanH_sas, thank you I tried it and I can understand its value. I will definitely remember to use it, but now I am required to display the chart in the previous manner. Also any help on displaying the 'N; below the time periods would be appreciated! ... View more