Those values also work. data have; input dob date_01 wrong ; cards; -1 23386 . -1 23386 . 0 22551 . 0 22551 . 0 22551 . 0 22551 . 0 22551 . 0 22551 . 0 22551 . 0 22551 . 0 22551 . 0 22551 . 0 22551 . 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 8 17322 47 9 19345 52 9 19345 52 9 19345 52 9 19345 52 9 19345 52 9 19345 52 ; proc sort nodupkey; by _all_; run; data want; set have; format dob date_01 mmddyy10.; right = int(yrdif(dob, date_01, 'AGE')); run; proc print; run; Results Obs dob date_01 wrong right 1 12/31/1959 01/11/2024 . 64 2 01/01/1960 09/28/2021 . 61 3 01/09/1960 06/05/2007 47 47 4 01/10/1960 12/18/2012 52 52 Check all of the steps in your log.  Make sure they completed and updated the output dataset. Make sure you are actually looking at the updated dataset.   ... View more

Something else is the problem.  Those dates work fine. data have; input dob :mmddyy. date_01 :mmddyy. wrong ; right = int(yrdif(dob, date_01, 'AGE')); format dob date_01 yymmdd10.; cards; 1/1/1960 4/28/1987 . 1/1/1960 9/28/2021 . 1/10/1960 12/18/2012 52 1/9/1960 6/5/2007 47 12/31/1959 1/11/2024 . ; Results Obs dob date_01 wrong right 1 1960-01-01 1987-04-28 . 27 2 1960-01-01 2021-09-28 . 61 3 1960-01-10 2012-12-18 52 52 4 1960-01-09 2007-06-05 47 47 5 1959-12-31 2024-01-11 . 64 Most likely the values in the listing you posted are CHARACTER strings that you tried to CONVERT into dates.  Check that step for the source of the errors. Most likely it either read the strings using the wrong informat (or wrongly placed so that it read only some of the string or parts of the strings next to the date).  Or perhaps there were tabs or other invisible characters in the strings that cause the conversion to fail.   Note that if they were DATE values printed with the MMDDYY10. format they would look like this instead: Obs dob date_01 wrong right 1 01/01/1960 04/28/1987 . 27 2 01/01/1960 09/28/2021 . 61 3 01/10/1960 12/18/2012 52 52 4 01/09/1960 06/05/2007 47 47 5 12/31/1959 01/11/2024 . 64 Notice how this listing is showing the leading zeros in single digit days and months that was missing in the text strings you posted.   ... View more

@Hakob wrote: How do you find the 3rd highest salary using a self-join? Can you provide more context please? Why SQL?  There are better tools for this type of question. ... View more

It didn't.  It created it from the actual dataset where the observations have values for both variables so you can count how many observations had GENDER=F and JOBCODE=MECH02.   Note that it created the first two REPORT the same way.   I try to avoid using the term "table" which has become overloaded with meanings.  Some people use it to refer to datasets instead of the original meaning of a tabular report like the ones you showed.     ... View more

Remember that FORMATs convert values into text. To convert text into values you need an INFORMAT.   So define the INFORMAT named $FPROVHF before trying to use it to create the character variable PROVINCE_CD by reading the values starting at column 2 of your text file.   Also make sure the default width is not longer than 4 or it might try to "eat" the values of BRANCH from columns 6 and beyond.   Are you sure you want to use a custom INFORMAT to read in the values?  Wouldn't it be better to just read in the values using the normal $ informat that would not try to modify them and then attach a FORMAT to the variable so it is displayed the way you want.   And what is with the use of the $CHAR informat for the other character variables? That will do two silly things.     For longer fields like NETWORK_PROVIDER $CHAR5. it will preserve any leading spaces.  Which can cause confusion because the value 'BC' will not not match '   BC', even if ODS outputs (like HTML) will make them appear the same.   And $CHAR will treat a value consisting of just a single period as a normal period, instead of translating it into spaces as the normal $ informat would.  The reason the $ informat does that is so you can use a period to indicate a missing (empty) value for both the numeric and the character variables. ... View more

That seems consistent with a cleanup script that is accidentally removing WORK folders while the SAS process that is using them is still running.   Another thing to check is virus scans (and other scans) of the disk where WORK directories are made that might be temporarily locking access.     Another thing to check is that the algorithm that is being used to generate the name of the directory used for work.  It that is accidentally causing two different processes to want to use the same name for the WORK library it might result in the first program having its work directory access removed because the new directory created for the second program is using different permission.  Or just plain deleting the files that the first program created. ... View more

@whymath wrote: Thank you. I am shocked, does that means the supervisor will return control to the top of data step, after running DOW-Loop? What's the usage of this feature? That is how the data step works.   It does not care (or even really know) that you put the SET statement inside a DO loop.   Most data steps are like your first one, they stop when they read past their inputs. 2 data _null_; 3 put _n_= eof= 'BEFORE'; 4 set oneobs end=eof; 5 put _n_= eof= 'AFTER'; 6 run; _N_=1 eof=0 BEFORE _N_=1 eof=1 AFTER _N_=2 eof=1 BEFORE NOTE: There were 1 observations read from the data set WORK.ONEOBS. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 second But you can explicitly tell it when to stop, like in your second one. 8 data _null_; 9 put _n_= eof= 'BEFORE'; 10 set oneobs end=eof; 11 put _n_= eof= 'AFTER'; 12 stop; 13 run; _N_=1 eof=0 BEFORE _N_=1 eof=1 AFTER NOTE: There were 1 observations read from the data set WORK.ONEOBS. NOTE: DATA statement used (Total process time): real time 0.01 seconds cpu time 0.00 seconds Or if there are no inputs at all then it knows not to start are second iteration. 15 data _null_; 16 put _n_= bmi= 'BEFORE'; 17 bmi=(180*0.4536)/(75*0.0254)**2; 18 put _n_= bmi= 'AFTER'; 19 run; _N_=1 bmi=. BEFORE _N_=1 bmi=22.498604997 AFTER NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds And if there are inputs but you complete an iteration without actually advancing any of the inputs then it stops and says it is because of looping. 21 data _null_; 22 put _n_= eof= 'BEFORE'; 23 if _N_=1 then set oneobs end=eof; 24 put _n_= eof= 'AFTER'; 25 run; _N_=1 eof=0 BEFORE _N_=1 eof=1 AFTER _N_=2 eof=1 BEFORE _N_=2 eof=1 AFTER NOTE: DATA STEP stopped due to looping. NOTE: There were 1 observations read from the data set WORK.ONEOBS. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds ... View more

I am not sure what you are saying is the issue.   Are you just talking about how SAS in general is displaying the variable ACTUAL?  Or is it something specific to PROC COMPARE is the issue?  Or perhaps to ODS EXCEL?   Is the variable named ACTUAL numeric or character?  What display format do you have attached to it in your two input SAS dataset?  Does it display the values the way you want?   ... View more

can you explain what you want to create it more detail?  It would help if you showed an example input dataset and the output you want from it.   Also explain the reasons for any restrictions. For example why does it matter if the column headers in the EXCEL file are character strings instead of numbers?   Note if you are using the YEAR value as the indication of what variable to transpose into then you probably do NOT need to use PROC TRANSPOSE.    You can just use a DATA STEP with an ARRAY that uses YEAR as the index.  Something like this: data wide; do until(last.id); set tall; by id; array m [2014:2025] y2014-y2025 ; if year in (2014:2025) then m[year] = value; end; drop year value ; run; And if you want an EXCEL file from your TALL dataset then you might not need to transpose it at all.  Just use ODS EXCEL and PROC REPROT to make the file. ods excel file='myfile.xslx'; proc report data=tall; column id value,year; define id / group; define year/ across ' '; define value / sum ' '; run; ods excel close; ... View more

If you really want to use looping try these loops. data want; set have ; array q q1-q20; flag=0; do index=1 to dim(q) while(flag=0); if 0 < q[index] then flag=1; end; do index=1 to dim(q) while(flag=0); if q[index] in (-7 -8 -9) then flag=.; else if q[index] ne 0 then put 'WARNING: Unexpected value. ' q[index]=; end; drop index; run;   ... View more

Does not look like you need to use looping.  Instead just use functions that can operate on multiple variables. data have; input q1-q20 flag_want; datalines; 20 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 20 20 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 20 0 -7 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 -8 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 . -8 -7 -7 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 . -7 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 1 ; data want; set have; if max(0,of q1-q20) > 0 then flag=1; else if whichn(-7,of q1-q20) or whichn(-8,of q1-q20) or whichn(-9,of q1-q20) then flag=.; else if 0=min(0,of q1-q20)=max(0,of q1-q20) then flag=0; run; And perhaps if those special values, -7, -8 and -9 are the only valid values that are less then zero then the second IF can be simpler.  For example you could use MIN() function. else min(0,of q1-q20)<0 then flag=.; Or if -7, -8 and -9 are really just different special missing codes if you actually code the data using special missing values (instead of valid numbers) you could use the NMISS() function.  For example by using .A, .B, and .C instead of -7, -8 and -9. missing abc ; data have; input q1-q20 flag_want; datalines; 20 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 20 20 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 20 0 A 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 B 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 . B A A 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 . A 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 1 ; data want; set have; if max(0,of q1-q20) > 0 then flag=1; else if 0<nmiss(of q1-q20) then flag=.; else if 0=min(0,of q1-q20)=max(0,of q1-q20) then flag=0; run;     ... View more

Because SAS (and most computer languages) use BINARY (base 2) floating point numbers.  Not the decimal numbers (base 10) that humans like to use.   None of those values can be exactly represented as binary numbers.  And when you add them all up the difference is enough that the normal equality test considers the values to be unequal.   Why not just the ROUND() function to reduce the number to a reasonable number of digits. data test; input x1-x4; total = sum(of x1-x4); diff = total-2; if total=2 then put TOTAL= 'is exactly 2.'; if round(total,0.0001)=2 then put TOTAl= 'is close enough to 2.' / 'since difference is ' diff:32.25 ; cards; .8 .6 .4 .2 ; ... View more

Another way to deal with this is to use an iterative DO loop (instead of using the DO OVER syntax that SAS has been trying to eliminate).  Then you can take advantage of the fact you can combine the iterative DO with an UNTIL() clause. data DSMESY2_test; set DSMESY2; array Ace ProblemSolving ReducingRisk BeingActive HealthyCoping HealthyEating Monitoring TakingMeds ; do index=1 to dim(Ace) until (AchievedGoal); AchievedGoal = (Ace[index]="Achieved"); end; drop index; run;   ... View more