BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
hayesk
Calcite | Level 5

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!

 
1 ACCEPTED SOLUTION

Accepted Solutions
13 REPLIES 13
Kurt_Bremser
Super User

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.

hayesk
Calcite | Level 5
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!
Kurt_Bremser
Super User

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.

hayesk
Calcite | Level 5
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

Reeza
Super User

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

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

hayesk
Calcite | Level 5

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

 

VariableTypeLengthFormatInformatLabel
Q2_2_Today_date_yearNum8Q2_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)

 

 

Reeza
Super User
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?

hayesk
Calcite | Level 5

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?

Kurt_Bremser
Super User

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;
hayesk
Calcite | Level 5

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

Kurt_Bremser
Super User

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.

hayesk
Calcite | Level 5
Lesson learned, thanks again 🙂
Kurt_Bremser
Super User

@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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 13 replies
  • 1288 views
  • 2 likes
  • 3 in conversation