Well, your second method is a data step.  Inside the data step, you have a WHERE with a Select.  As far as I know, Select is valid in Proc SQL but is not valid in a Data step.   There are multiple ways you  could get it working including a merge, a hash table, or arrays.   Given what you've already got, perhaps a merge is simplest.  Here is an example: Proc SORT DATA=Have; BY IP CustomerID; RUN; Proc SORT DATA=Tbl1; BY IP; RUN; Data wanted_way2_merge; MERGE Have (IN = Have) Tbl1 (IN = IP_List); BY IP; IF Have and IP_List; Run;   Jim ... View more

@sasuser_sk wrote: PROC SQL NOEXEC; SELECT (CASE when(t1.SRVC_ID_3Mos is missing ) then t1.SRVC_ID_6Mos else t1.SRVC_ID_6Mos END) AS CALCULATION FROM WORK.QUERY_FOR_AVG_3MONTHS_NONNULL t1; QUIT; Try removing the parenthesis after "END".   However, note, the code doesn't make sense to me.  Maybe I'm misunderstanding but it looks like that if the 3Mos value is missing the 6Mos value is used instead, but if it is not missing then the 6Mos value is also used.  Did you mean to use the 3Mos value if not missing?   Like this, maybe? PROC SQL NOEXEC; SELECT (CASE when(t1.SRVC_ID_3Mos is missing ) then t1.SRVC_ID_6Mos else t1.SRVC_ID_3Mos END AS CALCULATION FROM WORK.QUERY_FOR_AVG_3MONTHS_NONNULL t1; QUIT; Jim ... View more

@Elle,   I think you may be confusing the number of occurrences of the array with the length.   @Elle wrote: array word {&maxlength.} $100.; What you have in {braces} is not the length.  $100 is the length of each variable in the array.  "&maxlength" is not a length at all but rather is the number of occurrences in the array.   For example, let's say I have some variables in a SAS dataset.  Each variable is character.  The names of the variables are Animal1 - Animal6.  Let's say each variable is $32.   I would declare the array as follows: ARRAY Animals {6} $32 Animal1 - Animal6;   The number "6" is not a length but the number of occurrences.  $32 is the length of each occurrence.   For example, say I have the following SAS code:   DATA Have; INPUT Animal1 : $32. Animal2 : $32. Animal3 : $32. Animal4 : $32. Animal5 : $32. Animal6 : $32. ; DATALINES; bird cat aardvark tarantula tapir elephant dog owl mouse hippopotamus lamb lemur panda piranha parakeet pig peacock penquin armadillo fringehead jellyfish alligator wallaby pademelon ; RUN; DATA Want; DROP _:; SET Have; ARRAY Animals {6} $8 Animal1 - Animal6; DO _i = 1 TO 6; Animals{_i} = PROPCASE(Animals{_i}); END; RUN; PROC PRINT DATA = Want; RUN;     My array definition is  ARRAY Animals {6} $8 Animal1 - Animal6; Notice my results:  Some of the names are truncated.   Now, change the array definition from $8 to $32: ARRAY Animals {6} $32 Animal1 - Animal6; In the results, the truncation is gone.   In both of my definitions, the number of occurrences was {6}.  This did not change.  The length of each variable however did change.  It went from $8 to $32.  The length definition is what you need to change in order to fix truncation, not the number of occurrences. First definition (too short):   ARRAY Animals {6} $8 Animal1 - Animal6; Second definition (correct) ARRAY Animals {6} $32 Animal1 - Animal6;   Jim ... View more

OK, well, since I've seen you on the SAS Viya forum a few times, I'll take that as a hint to knock it off.  🙂   Jim ... View more

I hate to see posts languish, but I have no idea about this one.  I've seen @gwootton respond to several posts in this category (SAS Viya), but I'm probably going to get thrown out of SAS Communities if I keep bugging him.  I suppose he can always just tell me to knock it off if I'm not being helpful.  🙂   Jim ... View more

I am no expert on Proc X12.  I am however aware of Dr. Wicklin's (@Rick_SAS) blog post on mean imputation.  Perhaps mean imputation could be employed here.  However, Dr. Wicklin discusses some drawbacks to mean imputation, so I think be aware of those. https://blogs.sas.com/content/iml/2017/12/04/mean-imputation-sas.html I have no idea as to what analyses you are trying to conduct and whether or not mean imputation would invalidate your analyses. Dr. Wicklin recommends multiple imputation in general:  https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.3/statug/statug_mi_overview.htm   Jim ... View more

That's odd.   Well, I would try Firefox.  The result may be the same, but it's worth a try.   Jim ... View more

Is Internet Explorer your only option?  Do you have Edge, Chrome, or Firefox available?  Internet Explorer is quite out of date and is probably not what you want to be using.   Which SAS interface are you using?  Display Manger?  SAS studio?  It doesn't sound like you're using Enterprise Guide from what you're describing.   Jim ... View more

@ChrisNZ,   Select Unique?  I know it works in Oracle, but is that valid in SAS?  Is its behavior any different than Select Distinct?   Edit: DISTINCT eliminates duplicate rows. The DISTINCT argument is identical to UNIQUE. Alias UNIQUE Notes Although the UNIQUE argument is identical to DISTINCT, it is not an ANSI standard. Well, I'll be darned.  Learn something new every day.   Jim ... View more