Thank you !! I did this and with a few other steps and it worked 🙂    ... View more

Data want; Merge Y Z; by id visit_type; Run;   This gives me a data set with 13.000 observations. Multiple observations for each ID. Different variables, different ages. Only a couple of ages match from one file to the other. I need to make sure the ones that do match in age are only in one line instead of 2. Variables in Y are not the same as in Z aside from id visit_type and age.  but  age var has two different names (depending on the file) so it would not overwrite. one is age_yr the other is visit_age.  I am trying to do a comparison to see which ones match but then what ? So this way I can merge them but prevent the overwriting of the other variables... so what is not in Y  has to be missing and what is not in Z has to be missing.  I don't have a data step for that, it is what I am trying to figure out. I hope that helps. Thanks Mari     ... View more

yes.  Thank you. I changed the merge by id and age and it worked.  it is not overwriting. Now I have another issue: I need to be able to merge the data when the age is the same. I am not sure if I can explain but I will try. When I merge by 2 variables if prevents the overwrite and this was possible because I was talking about it with you here 🙂  But if I have data for the same age for example say I have measurements (from the one file) and diagnoses (from the other)  with same age 10.  They are in 2 different lines when they should be in one line only. so   ID        A B C     D E F    AGE         1        10 5 5    .   .   .     14          1        .    .  .    1  1  1  14   How do I tell sas to make this entry only one entry for those cases only? This happens in several instances in the data. Thank you so much   ... View more

Thank you. I changed to merge by id and age and it worked.  Now I have another issue: I need to be able to merge the data when the age is the same. I am not sure if I can explain but I will try. When I merge by 2 variables if prevents the overwrite. But if I have data for the same age for example I have measurements and diagnoses with age 10.  They are in 2 different lines when they should be in one line only. so ID   A B C     D E F    AGE         1      10 5 5    .   .   .     14     ID          1    .    .    .    1  1  1      14   How do I tell sas to make this entry only one entry ? This happens in several instances in the data. Thank you so much   ... View more

Y has height Body Weight variables  Z has diagnoses (URI ASTHMA etc) and drugs variables  X needs to have all variables from Y and Z. It is also longitudinal so one ID may have 5 entries in File Y and 10 in File Z. another ID might have 2 entries in File Y and 5 entries in file Z. All different ages. The variable that they both have in common is AGE and I found a way to prevent the ages from overwriting. So if I have one entry from Z and 10 from Y for ID 1- I have 11 entries with different ages  for ID 1 but then all of the diagnosis that were 1 in 1 ENTRY are repeated as 1 in all other 10 entries even though it should have been 1 only in ONE ENTRY and 0 in all the other 10 (from Y). I thought they would be missing and then I could tell sas to do if dx =. then dx=0. for example.  But with all diagnoses = 1 I don't have a way to give a command to transform it to 0. It will transform others (that must remain 1 to 0) - it will overwrite my correct data in Z. Every ID is different:  different ages and different amount of entries and diagnoses.    ... View more

In the example you gave (thank you) it is ID 1 that does not have ABC not ID2 so  so 12 13 14 on the second line of ID1 is the overwrite. because I am pulling 32 33 34 from another data file, and when I do so ID 1 (line 2) gets 12 13 14 when it needed to be missing (like ID2).   ... View more

Sorry this is getting to be such a long question. This age1 will give me a date and I am not sure this is what I wanted to do. Creating a date for when each ID turned one does not help because I might not have that date in the visit_ages for some IDS. Since we don't always have a visit for 1 year of age. Each ID has different ages, unfortunately. So if I am calculating age1 as the date for when each ID turns 1 what will happen for the IDS for which I don't have that date ?   Also my array is using variables VISIT_AGE not _VISIT_DATE.   I do have visit dates (but I might not have that 1 year old date) and I am using age variables so wouldn't it be better - and more importantly - is there a way for me to specify the following in the array ?   only if visit_age =< 1? but this would need to be for all visit_ages Visit_age1 visit_age2 etc.     If I need to pull out  antibiotics coded 1 (yes) only Up to 1 year of age and there is  way for me to specify the above in the array then  the OUTVARS in my array would only be generated IF AND only the ages were 1 or lower.      The visit_ages are anywhere from 0.1 to 5.0 So the 25 visits can have a big range covered (unfortunately).   The ages are NOT the same for the visits. So Visit1_age could be 0.1 for one ID and 5.0 for another.  That is my issue with this data set.   Creating age1 is not doing that because it is creating a DATE (correct?) and that date might not be in my visit_date variables if I don' t have a visit when the child turned 1.   I used my visit_date variables to create the visit_age variables because I thought that would be easier. But for that I need a command that would specify this in the array command I have going.    Thank you so much for your patience 🙂    ... View more

that works !! Thank you. When I create the spss file age is in a weird format 16071 I am assuming sas format ?   It should have been 0.1  0.5   0.6  etc. What to do ? format it ? how?   And all of the other outvars I am trying to create are still empty.    This error remains Invalid numeric data, 'antibi1' , so I have to figure this one out. It is weird because I ran frequencies and the var antibi 1 is fine it is 0 and 1 like all the other ones.    Really weird but so close though.  Thanks again     ... View more

yes I just realized that right after I  posted my last note. This is what I add to create age1  age1 = intnx(year,dob,1); /* add 1 year to date of birth */ and kept the rest of the code the same and the variables are created but all empty ?   these are new errors   Argument 1 to function INTNX(' .',15803,1) at line 102 column 8 is invalid   and It looks like it is only for a few ids ? but it would be able to create for others ? wouldn't it ?    and at the end it says   Mathematical operations could not be performed at the following places. The results of the operations have been set to missing values.     This is what my code looks like now   is there anything wrong that I am missing ? Thank you for your help !    Data want; Set have;   age1 = intnx(year,dob,1); /* add 1 year to date of birth */ array inage {25} VISIT1_AGE VISIT2_AGE VISIT3_AGE VISIT4_AGE VISIT5_AGE VISIT6_AGE VISIT7_AGE VISIT8_AGE VISIT9_AGE VISIT10_AGE VISIT11_AGE VISIT12_AGE VISIT13_AGE VISIT14_AGE VISIT15_AGE VISIT16_AGE VISIT17_AGE VISIT18_AGE VISIT19_AGE VISIT20_AGE VISIT21_AGE VISIT22_AGE VISIT23_AGE VISIT24_AGE VISIT25_AGE; array incode {25} antibi1 antibi2 antibi3 antibi4 antibi5 antibi6 antibi7 antibi8 antibi9 antibi10 antibi11 antibi12 antibi13 antibi14 antibi15 antibi16 antibi17 antibi18 antibi19 antibi20 antibi21 antibi22 antibi23 antibi24 antibi25; array outage {25} outVISIT1_AGE outVISIT2_AGE outVISIT3_AGE outVISIT4_AGE outVISIT5_AGE outVISIT6_AGE outVISIT7_AGE outVISIT8_AGE outVISIT9_AGE outVISIT10_AGE outVISIT11_AGE VISIT12_AGE outVISIT13_AGE outVISIT14_AGE outVISIT15_AGE outVISIT16_AGE outVISIT17_AGE outVISIT18_AGE outVISIT19_AGE outVISIT20_AGE outVISIT21_AGE outVISIT22_AGE outVISIT23_AGE outVISIT24_AGE outVISIT25_AGE; array outcode {25} outantibi1 - outantibi25; j = 1; do i = 1 to 25; if (. < inage{i} le age1) and incode{i} in('antibi1' 'antibi2' 'antibi3' 'antibi4' 'antibi5' 'antibi6' 'antibi7' 'antibi8' 'antibi9' 'antibi10' 'antibi11' 'antibi12' 'antibi13' 'antibi14' 'antibi15' 'antibi16' 'antibi17' 'antibi18' 'antibi19' 'antibi20' 'antibi21' 'antibi22' 'antibi23' 'antibi24' 'antibi25') then do; /* if age is not missing and in time frame and you have one or more of the asthma codes */ outage{j} = inage{i}; /* move image to outage */ outcode{j} = 1; /* found antibi code and in time range*/ j = j + 1; /* when you find one then increment the j index */ end; end; ... View more

Disregard my previous comment. It worked as it showed no errors. However, when I create an SPSS file this is what it says    age1 is uninitialized so it is creating a var age1   from here  if (. < inage{i} le age1) and incode{i} in('antibi1' 'antibi2' 'antibi3' 'antibi4' 'antibi5' 'antibi6' 'antibi7       ... View more

ok so when I do that this is what I get array inage {25} VISIT1_AGE VISIT2_AGE VISIT3_AGE VISIT4_AGE VISIT5_AGE VISIT6_AGE VISIT7_AGE ----- 180 233! VISIT8_AGE VISIT9_AGE VISIT10_AGE VISIT11_AGE ERROR 180-322: Statement is not valid or it is used out of proper order. 234 VISIT12_AGE VISIT13_AGE VISIT14_AGE VISIT15_AGE VISIT16_AGE VISIT17_AGE VISIT18_AGE 234! VISIT19_AGE VISIT20_AGE VISIT21_AGE VISIT22_AGE VISIT23_AGE VISIT24_AGE VISIT25; 235 array incode {25} antibi1 antibi2 antibi3 antibi4 antibi5 antibi6 antibi7 antibi8 antibi9 ----- 180 235! antibi10 antibi11 antibi12 antibi13 antibi14 antibi15 antibi16 ERROR 180-322: Statement is not valid or it is used out of proper order. 236 antibi17 antibi18 antibi19 antibi20 antibi21 antibi22 antibi23 antibi24 antibi25; 237 array outage {25} VISIT1_AGE VISIT2_AGE VISIT3_AGE VISIT4_AGE VISIT5_AGE VISIT6_AGE ----- 180 237! VISIT7_AGE VISIT8_AGE VISIT9_AGE VISIT10_AGE VISIT11_AGE ERROR 180-322: Statement is not valid or it is used out of proper order. 238 VISIT12_AGE VISIT13_AGE VISIT14_AGE VISIT15_AGE VISIT16_AGE VISIT17_AGE VISIT18_AGE 238! VISIT19_AGE VISIT20_AGE VISIT21_AGE VISIT22_AGE VISIT23_AGE VISIT24_AGE VISIT25; 239 array outcode {25} outantibi1- outantibi25; ----- 180 ERROR 180-322: Statement is not valid or it is used out of proper order. 240 j = 1; - 180 ERROR 180-322: Statement is not valid or it is used out of proper order. 241 242 do i = 1 to 25; -- 180 ERROR 180-322: Statement is not valid or it is used out of proper order. 243 if (. < inage{i} le age1) and incode{i} in('antibi1' 'antibi2' 'antibi3' 'antibi4' -- 180 243! 'antibi5' 'antibi6' 'antibi7' ERROR 180-322: Statement is not valid or it is used out of proper order. 244 'antibi8' 'antibi9' 'antibi10' 'antibi11' 'antibi12' 'antibi13' 'antibi14' 'antibi15' 244! 'antibi16' 'antibi17' 245 'antibi18' 'antibi19' 'antibi20' 'antibi21' 'antibi22' 'antibi23' 'antibi24' 'antibi25') then 245! do; /* if age is not missing and in time frame and you have one or more of the asthma codes 245! /* if age is not missing and in time frame and you have one or more of the asthma codes */ 246 outage{j} = inage{i}; ------ 180 ERROR 180-322: Statement is not valid or it is used out of proper order. 247 outcode{j} = 1; ------- 180 ERROR 180-322: Statement is not valid or it is used out of proper order. 248 j = j + 1; - 180 ERROR 180-322: Statement is not valid or it is used out of proper order. 249 end; --- 180 ERROR 180-322: Statement is not valid or it is used out of proper order.     ... View more

Hi Astounding,   Sorry it took me awhile to get back to this.   I ended up calculating my ages and I am trying an array for antibiotic use for age up to 12 months (1 year). Trying to find out which ids got antibiotic coded before age 1 year.   I am running into errors and can't figure out why.    Data want; set have;   array inage {25} VISIT1_AGE VISIT2_AGE VISIT3_AGE VISIT4_AGE VISIT5_AGE VISIT6_AGE VISIT7_AGE VISIT8_AGE VISIT9_AGE VISIT10_AGE VISIT11_AGE VISIT12_AGE VISIT13_AGE VISIT14_AGE VISIT15_AGE VISIT16_AGE VISIT17_AGE VISIT18_AGE VISIT19_AGE VISIT20_AGE VISIT21_AGE VISIT22_AGE VISIT23_AGE VISIT24_AGE VISIT25; array incode {25} antibi1 antibi2 antibi3 antibi4 antibi5 antibi6 antibi7 antibi8 antibi9 antibi10 antibi11 antibi12 antibi13 antibi14 antibi15 antibi16 antibi17 antibi18 antibi19 antibi20 antibi21 antibi22 antibi23 antibi24 antibi25; array outage {25} outVISIT1_AGE -- outVISIT25_AGE ; array outcode {25} outantibi1--outantibi25; j = 1; do i = 1 to 25; if (. < inage{i} le age1) and incode{i} in('antibi1' 'antibi2' 'antibi3' 'antibi4' 'antibi5' 'antibi6' 'antibi7' 'antibi8' 'antibi9' 'antibi10' 'antibi11' 'antibi12' 'antibi13' 'antibi14' 'antibi15' 'antibi16' 'antibi17' 'antibi18' 'antibi19' 'antibi20' 'antibi21' 'antibi22' 'antibi23' 'antibi24' 'antibi25') then do; /* if age is not missing and in time frame and you have one or more of the asthma codes */ outage{j} = inage{i}; /* move image to outage */ outcode{j} = 1; /* found antibi code and in time range*/ j = j + 1; /* when you find one then increment the j index */ end;   These are the errors   ERROR: Variable outVISIT1_AGE cannot be found on the list of previously defined variables. ERROR: Too few variables defined for the dimension(s) specified for the array outage. 197 array inage {25} VISIT1_AGE VISIT2_AGE VISIT3_AGE VISIT4_AGE VISIT5_AGE VISIT6_AGE VISIT7_AGE ----- 124 197! VISIT8_AGE VISIT9_AGE VISIT10_AGE VISIT11_AGE ERROR: Variable outantibi1 cannot be found on the list of previously defined variables. ERROR: Too few variables defined for the dimension(s) specified for the array outcode. ERROR 124-185: The variable inage has already been defined. 198 VISIT12_AGE VISIT13_AGE VISIT14_AGE VISIT15_AGE VISIT16_AGE VISIT17_AGE VISIT18_AGE 198! VISIT19_AGE VISIT20_AGE VISIT21_AGE VISIT22_AGE VISIT23_AGE VISIT24_AGE VISIT25; 199 array incode {25} antibi1 antibi2 antibi3 antibi4 antibi5 antibi6 antibi7 antibi8 antibi9 ------ 124 199! antibi10 antibi11 antibi12 antibi13 antibi14 antibi15 antibi16 ERROR 124-185: The variable incode has already been defined. 200 antibi17 antibi18 antibi19 antibi20 antibi21 antibi22 antibi23 antibi24 antibi25; 201 array outage {25} outVISIT1_AGE -- outVISIT25_AGE ; ------ 124 ERROR 124-185: The variable outage has already been defined. 202 array outcode {25} outantibi1--outantibi25; ------- 124 ERROR 124-185: The variable outcode has already been defined. 203 j = 1; 204 205 do i = 1 to 25; 206 if (. < inage{i} le age1) and incode{i} in('antibi1' 'antibi2' 'antibi3' 'antibi4' 206! 'antibi5' 'antibi6' 'antibi7' 207 'antibi8' 'antibi9' 'antibi10' 'antibi11' 'antibi12' 'antibi13' 'antibi14' 'antibi15' 207! 'antibi16' 'antibi17' 208 'antibi18' 'antibi19' 'antibi20' 'antibi21' 'antibi22' 'antibi23' 'antibi24' 'antibi25') then 208! do; /* if age is not missing and in time frame and you have one or more of antibi coded 208! */ 209 outage{j} = inage{i}; /* move image to outage */ 210 outcode{j} = 1; /* found antibi code and in time range*/ 211 j = j + 1; /* when you find one then increment the j index */ 212 end;   I have been trying to figure it out unsuccessfully. I appreciate any input to point me in the right direction.   Thank you again   ... View more