topic Re: Year showing up correctly on SAS but not in Excel export in New SAS User

Year showing up correctly on SAS but not in Excel export

hayesk — Fri, 19 Aug 2022 18:49:51 GMT

I have a large dataset with 3 variables to comprise a date: Q2_Today_date_month, Q2_1_Today_date_day, Q2_2_Today_date_year.

The values for the month and day are showing up correctly, but the value for the year appears correct on the SAS output but after the proc export to an Excel workbook, the year changes to a 3-digit value of 120 or 121. The years should be between 2019-2021.

In SAS, the year variable is just a regular numeric variable. When I try to format it to a date, it becomes 1960. I know the year 1960 is the default SAS year but I'm confused how numeric values of 2019, 2020, and 2021 are translating to a year of 1960. I'm not sure if the solution is changing the format in Excel or in SAS since it appears correct in SAS but then gets messed up when opening the dataset in Excel. Any guidance is greatly appreciated, thanks!

Re: Year showing up correctly on SAS but not in Excel export

Kurt_Bremser — Fri, 19 Aug 2022 19:07:36 GMT

A numeric value of 2000-something would be a SAS date in 1966 or 1967 (2000+ days after 1960-01-01).

A date in 1960 can only result from values below 366.

So it seems your actual values are in the three-digit range and only displayed differently because of a special format.

Re: Year showing up correctly on SAS but not in Excel export

hayesk — Fri, 19 Aug 2022 19:18:56 GMT

Hmm… the data is being downloaded from a survey on Qualtrics and the only response choices are in 4-digit numeric years. There’s also no special format Im applying to the variable. If I import the data file into SAS and immediately print the dataset, the year shows up as 2019, 2020, or 2021. Unsure how it could be importing as a 3 digit value but SAS still somehow reads the numeric value as the correct year but then it gets all messed up in the date reformatting. I’m a new user by the way so if you have any advice on how you would proceed that would be really helpful! Thanks!

Re: Year showing up correctly on SAS but not in Excel export

Kurt_Bremser — Fri, 19 Aug 2022 19:29:31 GMT

Please post your code used to export to Excel.

If in doubt, write the data to a semicolon-delimited file with extension .csv, then look at that file with a text editor before reading it with Excel.

Re: Year showing up correctly on SAS but not in Excel export

Reeza — Fri, 19 Aug 2022 19:29:40 GMT

What is the format/type on the year column in SAS?

What is the format/display of the column in Excel?

Re: Year showing up correctly on SAS but not in Excel export

hayesk — Fri, 19 Aug 2022 19:53:12 GMT

Here is the output from the proc contents for the year variable:

Variable	Type	Length	Format	Informat	Label
Q2_2_Today_date_year	Num	8	Q2_2_TOA.		Today's date: - Year

In Excel, the column format just says "General" but if I change it to date then it makes it 1900. And all the values in that column are 120 or 121 as I mentioned but in SAS it shows up as the correct years (2019, 2020, 2021)

Re: Year showing up correctly on SAS but not in Excel export

hayesk — Fri, 19 Aug 2022 20:01:42 GMT

proc export data=qd outfile='G:...'
	dbms=xlsx replace;
run;

Here's my export code, I redacted the file location and name but that would normally be there in place of the '...'.

I followed your advice and exported it as csv and then read in a text editor and the year is still showing up correctly there! Not sure where it's all going wrong

Re: Year showing up correctly on SAS but not in Excel export

Reeza — Fri, 19 Aug 2022 20:05:45 GMT

proc freq data=YourDatasetName;
table q2_2_today_year_num;
format q2_2_today_year_num 8.;
run;

proc format cntlout=want;
select Q2_2_TOA;
run;

proc print data=want;
run;

What does that show? Can you show the output?

Re: Year showing up correctly on SAS but not in Excel export

Kurt_Bremser — Fri, 19 Aug 2022 20:16:05 GMT

@hayesk wrote:

Here is the output from the proc contents for the year variable:

Variable Type Length Format Informat Label

Q2_2_Today_date_year Num 8 Q2_2_TOA. Today's date: - Year

In Excel, the column format just says "General" but if I change it to date then it makes it 1900. And all the values in that column are 120 or 121 as I mentioned but in SAS it shows up as the correct years (2019, 2020, 2021)

So you do have a non-standard format (Q2_2_TOA) which displays the internal value as 4-digit years.

Since PROC EXPORT does recognize this column only as a simple number, the raw value ends up in Excel.

By using a text file where you PUT the formatted value, you can avoid this.

Re: Year showing up correctly on SAS but not in Excel export

hayesk — Fri, 19 Aug 2022 20:16:49 GMT

I'm attaching the output as a document but I think you just helped me figure it out! Should I just reformat these to equal the year or use if statements or what do you recommend?

Re: Year showing up correctly on SAS but not in Excel export

Kurt_Bremser — Fri, 19 Aug 2022 20:32:48 GMT

Convert your variable before exporting:

data qd_ex;
set qd (rename=(Q2_2_Today_date_year=_year));
Q2_2_Today_date_year = input(put(_year,q2_2_toa.),4.);
drop _year;
run;

Re: Year showing up correctly on SAS but not in Excel export

hayesk — Fri, 19 Aug 2022 20:36:05 GMT

IT WORKED!!! Thank you so much you saved me at work 🙂

Re: Year showing up correctly on SAS but not in Excel export

Kurt_Bremser — Fri, 19 Aug 2022 20:42:05 GMT

This whole thread is a nice illustration for my Maxim 3: Know Your Data.

And may I quote myself from my first post in this thread:

So it seems your actual values are in the three-digit range and only displayed differently because of a special format.

Re: Year showing up correctly on SAS but not in Excel export

hayesk — Fri, 19 Aug 2022 20:44:03 GMT

Lesson learned, thanks again 🙂