There is really no such thing as a tool or program that will perfectly validate or check your data. However there are techniques in SAS that can help you like this: proc freq data = sashelp.class; table _numeric_; run; This program will count discrete levels of all numeric variables in the chosen dataset. Obviously this is only useful for non-continuous numerics but it gives you an idea as to how you can approach validation.   Edit: An example of how to check for missing or zero values: data test; set sashelp.class; output; if name in ('Carol','Thomas') then do; age = 0; height = . ; weight = 0; output; end; run; data test; drop i; set test; array nums (*) _numeric_ ; do i = 1 to dim(nums); if nums(i) in (.) then Missing_Flag = 'Y'; if nums(i) in (0) then Zero_Flag = 'Y'; end; run; proc freq data = test; where Missing_Flag = 'Y' or Zero_Flag = 'Y'; table name * _numeric_ / missing list; run; ... View more

Thank you so much 😊 there’s a lot of information. ... View more

Thank you 🙏 ... View more

@ranikeka wrote: Many Thanks   can i use same code for below one as well   sheet1 abc  def ghi 1 a a1 2 b a2 3 c a3 4 d a4 sheet 2 jkl mno pqr xxxx rrrr tttt vvvvv hhhh tttt nnnnn ggg gg sssss bbb jj eeeee ccc kk tttt hhhh nnn tttt hhhh nnn tttt hhhh nnn fffff tttt uuuu pppp qqqq eeee tttt ggg tttt lll ppp iii   I want to combine two above sheets like following abc  def ghi jkl mno pqr 1 a a1 xxxx rrrr tttt 1 a a1 vvvvv hhhh tttt 1 a a1 nnnnn ggg gg 1 a a1 sssss bbb jj 1 a a1 eeeee ccc kk 2 b a2 tttt hhhh nnn 2 b a2 tttt hhhh nnn 2 b a2 tttt hhhh nnn 3 c a3 fffff tttt uuuu 3 c a3 pppp qqqq eeee 3 c a3 tttt ggg tttt 3 c a3 lll ppp iii In the absence of an apparent matching rule, it looks like you are matching 1 record in sheet1 to a batch of 4 records in sheet2, in order encountered.  If that's true, you can do the below.   But just remember - one misplaced or deleted or duplicated record in either sheet will generate unwanted results, and there will be no obvious way to recognize that outcome. data sheet1; input abc $ def $ ghi $; datalines; 1 a a1 2 b a2 3 c a3 4 d a4 ; data sheet2; input jkl $ mno $ pqr $; datalines; xxxx rrrr tttt vvvvv hhhh tttt nnnnn ggg gg sssss bbb jj eeeee ccc kk tttt hhhh nnn tttt hhhh nnn tttt hhhh nnn fffff tttt uuuu pppp qqqq eeee tttt ggg tttt lll ppp iii run; data want; set sheet1; do i=1 to 4; set sheet2; output; end; run;   Note the log of this (below) reports reading 4 observations from sheet1, but that 4th obs (abc=4) is not on the output dataset.  That's because, with the 4th sheet1 record in hand, the program attempts to read the 13th from sheet2.  But there is no 13th record in sheet2, so the data step stops, with no further processing - i.e. no outputting of the record. NOTE: There were 4 observations read from the data set WORK.SHEET1. NOTE: There were 12 observations read from the data set WORK.SHEET2. NOTE: The data set WORK.WANT has 12 observations and 7 variables. ... View more

The proper way of avoiding this in a production environment is, of course, to avoid Excel files altogether. Save the data to a sensible format (CSV or similar), which you can read with a consistent data step. ... View more

Tom, although it's perfectly understood, is it possible that there's a typo in the third "put"? Should be "sheet1" the base file? ... View more