SAS does not change DATE format!

[ali11]

Hi! I'm trying to obtain monthly format (monyy7) from an already existing yymmdd10. format. I have tried every thing I thought of but SAS keeps spitting out ****** instead. The log does not show error. What do you think the problem should be?

Here is what I try:

data monthly;
 set subsample;
 monyy=put(report_date,monyy7.);
run;

Any ideas?

[novinosrin]

If report_date is char then 

check with proc contents

data monthly;
 set subsample;
 monyy=put(input(report_date,yymmdd10.),monyy7.);
run;

[ali11]

Just checked, it is not char but Num.

[novinosrin]

Well methinks your code is correct. Can you post a few records of your output and your log?

[ali11]

just added a photo of my data

[ballardw · Accepted solution]

---

@ali11 wrote:

Just checked, it is not char but Num.

---

And what is the actual current format assigned?

Quite often we see people with a simple numeric value that they think is date. You will get the ******* when the "date" value exceeds year 9999

Example:

data example;
   x='01Jan2019'd;
   y= put(x,monyy7.);
   put x= yymmdd10. y=;
   x='01Jan19999'd;
   y= put(x,monyy7.);
   put x= yymmdd10. y=;
   x=20190101;
   y= put(x,monyy7.);
   put x= yymmdd10. y=;
run;

If you are seeing all ******* I suspect your date is a simple numeric with a format like BEST12. and values like 20190101, not actually date values.

[ali11]

The dataset guide mentions that the date format is YYYYMMDD, what I see, as you mention, is numbers like 20190105. Then if this is the problem, any idea about how can I make SAS recognize it in Date format? Since the data is widely used I suspect they have made such mistakes though.

[ali11]

actually this was the case I solved it by
data f_date;
set subsample;
f_date = input(put(report_date,8.),yymmdd8.);
format date;
format f_date monyy7.;
run;
thanks!

[ballardw]

---

@ali11 wrote:
actually this was the case I solved it by
data f_date;
set subsample;
f_date = input(put(report_date,8.),yymmdd8.);
format date;
format f_date monyy7.;
run;
thanks!

---

Something like this when given a reasonable data dictionary is the best approach to reading the data initially. I might guess that you first used proc import and it was "close enough" not to worry. One of the big concerns with Proc Import if you are going to deal with multiple input sets of the same structure is that it will make a different set of guesses each time you import a new file. Which means that variables can change data type, format and lengths. If the column headings change you also can get different variable names. So you get into a number of issues when combining multiple sets.

Best is to write a data step with the proper informat and setting the lengths and properties from the data dictionary. Then you just change the name of the infile and output data set and the data will be much easier to work with.

[PaigeMiller]

---

@ali11 wrote:

Hi! I'm trying to obtain monthly format (monyy7) from an already existing yymmdd10. format. I have tried every thing I thought of but SAS keeps spitting out ****** instead. The log does not show error. What do you think the problem should be?

Here is what I try:

data monthly;
 set subsample;
 monyy=put(report_date,monyy7.);
run;

---

The most common reason for this to happen is that you have an actual SAS date/time value, and not a SAS date value. What happens if instead of monyy7. you use datetime16. ? Does it work then?

or perhaps

monyy=put(datepart(report_date),monyy7.);

But really, why create a character variable monyy anyway, why not just leave it as numeric and format it properly?

[ali11]

Thanks for the hints. I used both ways and it is still not working.

 monyy=put(report_date,datetime16.); returns some wired dates that do not match with I have and 

monyy=put(datepart(report_date),monyy7.); returns AUG1960 for every observation in my report_date. 

report_date belongs to 201706. It seems datepart(report_date) picks the wrong order for example 20170601 is reported AUG60.

Very good question about why I need this, I have daily transaction data for different companies. I need to aggregate the data by company per month. Since I use PROC MEANS, for the CLASS part of the code if I provide report_date (daily) the code does not spit out what I need, Now, if there is a better way to do this aggravation I'll forget about the date conversion 🙂 and here is what my data looks like after using  monyy=put(report_date,datetime16.);

[Tom]

SAS stores numbers are floating values. What the value prints like depends on what format you tell SAS to use when converting the value to text for display.  When you look at the output of PROC PRINT or browse the dataset in some viewer tool you are seeing the formatted value and not the actual value.

So to tell what type of values you actually have you need look at the value printed using a format that you know how to interpret.  To know what format is attached to the variable when you look the definition of your dataset you need check both the TYPE of the variable (numeric or character) and what format (if any) has been attached to it.  If no format is attached to a numeric variable then SAS will typically use the BEST12. format.

DATE formats will interpret the number as the number of days since 1960. DATETIME formats will interpret the number as the number of seconds since 1960.  The DATEPART() function essentially just divides the value by the number of seconds in a day.  So if you run a date value through it you will get a value that is essentially January 1st, 1960. 

If you just have a numeric variable with a value of 20,170,601 in then it will normally print looking like 20170601.  If you use COMMA format then it will put in the thousands separators and it will be much clearer that you have a number and not a date.  The date '01JUN2017'D is stored as the value 20,971.

If you have the number 20170601 you can convert it to a date by first using the 8. or Z8. format to convert it to an 8 digit string. And then use the YYMMDD informat to convert that string to a date.  Or you could use some arithmetic and pull out the year, month and day numbers and use the MDY() function to convert them to a date value.

Example:

51    data test;
52      have=20170601 ;
53      date1=input(put(have,z8.),yymmdd8.);
54      date2=mdy(int(mod(have,10000)/100),mod(have,100),int(have/10000));
55      put have= comma12. date1 = date9. date2 = date9.;
56    run;

have=20,170,601 date1=01JUN2017 date2=01JUN2017

[ali11]

thanks for the clarification. this is so much help.

[PaigeMiller]

Your screen capture shows that you do not have valid SAS date values, and you do not have valid SAS date/time values. You have an integer that looks to humans like a date, but it is not a date according to SAS. SAS stores dates as the number of days after January 1, 1960, so today (October 25, 2019) is stored by SAS as 21847.

You can use the ANYDTDTE informat to turn 20170602 (and similar integers) into something that is recognized by SAS as an actual date.

date=input(put(report_date,8.),anydtdte.);
format date monyy7.;

Very good question about why I need this, I have daily transaction data for different companies. I need to aggregate the data by company per month. Since I use PROC MEANS, for the CLASS part of the code if I provide report_date (daily) the code does not spit out what I need, Now, if there is a better way to do this aggravation I'll forget about the date conversion and here is what my data looks like after using  monyy=put(report_date,datetime16.);

You don't need a character variable with values such as 'JUL2017' to do almost any reporting/aggregating in SAS. You simply need the format applied to a proper numeric SAS date value, as I have shown in my code. Then, any aggregating (PROC MEANS, PROC SUMMARY, PROC FREQ, etc.) of this formatted date value will be controlled by the format, and so in the case of the above code, aggregating will be done by month/year (because you have assigned the MONYY format to variable DATE). Naturally, you could also use a week or quarter or year format to aggregate by other calendar intervals.

[ali11]

Thanks Paige. This is very helpful to modify my code to aggregate more efficiently.