topic Re: Replacing observations for date variable with wrong formatting from stata in SAS Programming

Replacing observations for date variable with wrong formatting from stata

ly2105 — Sun, 26 Aug 2018 17:37:51 GMT

I imported a stata dataset into SAS successfully. However, for my date variable (r24_date), several of the observations had typos such that 16oct2015 is actually coded as16oct0215. I can see this when looking at the stata dataset in stata. Something goes wrong here when I import the stata dataset into SAS and "16oct0215" becomes **********.

For example, printing out a single observation (after sorting the dataset by r24_date ascending):

Other than this one date typo, the remainder of the dates are correct and are in the format MMDDYY10.

For example:

I've tried to replace the ******** using a simple datastep, but SAS does not like the **********s.

data want;
set have;

if r24_date = "**********" then r24_date = "16oct2015"d;
run;

I have tried changing the data in stata and in excel then importing to SAS, but I am still having errors related to date formats.

Any thoughts? Thanks.

Re: Replacing observations for date variable with wrong formatting from stata

Tom — Sun, 26 Aug 2018 19:38:15 GMT

Are you sure the values are actually **** or is the way that you are looking at it just showing that because the actual value is too large?

The only way the value could be *** is if the variable is a character string instead of a date.

Look at the actual unformatted values.

Re: Replacing observations for date variable with wrong formatting from stata

ly2105 — Sun, 26 Aug 2018 19:50:40 GMT

Thanks for the tip- you're correct.

I looked at it with no formatting applied and in fact the value is -637060. Using the following code was successful in changing the values.

data want; 
	set have; 
	if r24_date = -637060 then r24_date = "16oct2015"d; 
run;

Re: Replacing observations for date variable with wrong formatting from stata

Tom — Sun, 26 Aug 2018 20:05:39 GMT

SAS does not accept dates in year 215. There are so many changes to calendars that it is not really straight forward how to represent them as integers.

But that value does look like something that might be in the year 215.

292  data x;
293   x=-637060 ;
294   month=month(x);
295   day=day(x);
296   year=year(x);
297   year2=1960-int(-x/365);
298   put (_all_) (=);
299  run;

NOTE: Invalid argument to function MONTH(-637060) at line 294 column 8.
NOTE: Invalid argument to function DAY(-637060) at line 295 column 6.
NOTE: Invalid argument to function YEAR(-637060) at line 296 column 7.
x=-637060 month=. day=. year=. year2=215
x=-637060 month=. day=. year=. year2=215 _ERROR_=1 _N_=1
NOTE: Mathematical operations could not be performed at the following places. The results of the
      operations have been set to missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1 at 294:8   1 at 295:6   1 at 296:7
NOTE: The data set WORK.X has 1 observations and 5 variables.

Re: Replacing observations for date variable with wrong formatting from stata

ballardw — Mon, 27 Aug 2018 16:19:39 GMT

Any date that might be considered as prior to 1518, The English adoption of the Gregorian calendar will yield an invalid date and the SAS formats will display the ********** you saw.

If you have a "year" greater than 9999 then SAS will display the year portion of the format as **** or **