BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
confused_saser
Obsidian | Level 7

I have had to import a number of csv files an di have successful appended all of them. Before being able to append them I had to convert all my date variables into SAS date variables (format DATE9.). 

 

I thought all was good until I exported my appended dataset and discovered that all my date variables are showing up as asterisks. However, when I open up the viewtable in SAS I can still see values there (e.g., 20160131)

 

Why are all my values showing up as asterisks when I export as an excel or csv? And why are they showing up semi-normally in the SAS viewtable?

 

What do I need to do to have the dates work properly so that I can run analyses on them? 

 

thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
confused_saser
Obsidian | Level 7

I have a way easier way of solving this problem!

 

Before I imported all my csv files I converted all my date variables into EXCEL DATES FIRST using the text to column function in excel. 

 

This solved EVERYTHING! 

View solution in original post

6 REPLIES 6
Reeza
Super User

Make sure that you're viewing the CSV in a text editor to verify if the issue is with the SAS Export or Excel interpretation of the file 

 

You don't say how you exported the file, but they usually show up as asterisks when the width isn't wide enough to hold the value. If you're looking at the data in excel they'll show up as hashtags if the column isn't wide enough as well. 

ballardw
Super User

I will be a large number of $$ that your value is a numeric with the value of 20160131 which is not a DATE value.

 

Please see this code snippet on how to make such a number into a date.

data _null_;
   x=20160131;
   x = input(put(x,f8.0),yymmdd8.);

   put x mmddyy10.;
run;

confused_saser
Obsidian | Level 7

I have a way easier way of solving this problem!

 

Before I imported all my csv files I converted all my date variables into EXCEL DATES FIRST using the text to column function in excel. 

 

This solved EVERYTHING! 

ballardw
Super User

If the file was orginally CSV and you edit it with Excel you want to go over the results with a fine tooth comb to make sure the correct date conversion was applied.

 

Also Excel is known to change some types of character values to numerics under some circumstances. So account or product "numbers" that look like 45-123 end up with "values" of -78 or 1-5 comes a "date" of May 1 in the current year.

NeerajS1104
Fluorite | Level 6

Superb code .

I was stuck since two days due to this issue.

Should have come to this answer earlier .

 

Thanks a lot .

Made my day.

 

Good day wishes.

Peter_C
Rhodochrosite | Level 12
A date column presenting with asterisks is not missing - it is outside the valid numeric range for a SAS date (something like 1Jan1582 to 31Dec20000)
One way this happens: a datetime value is formatted as a date - some SAS ACCESS routines can cause this.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 17105 views
  • 6 likes
  • 5 in conversation