BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
I use proc import to create sas datasets from excel files.
One of the fields patient_id , the first two records are 0711F08* and the rest of the records are numbers. SAS leaves a blank for the first two records for the patient id.
Without manually changing the data, is there anyway to import all the records?
4 REPLIES 4
Peter_C
Rhodochrosite | Level 12
option
mixed=yes
should allow the entire column to be treated as text.

Have you considered using the excel libname engine?

PeterC
SASPhile
Quartz | Level 8
Peter,
Thanks for the help.When I use option mixed =yes, the date filed is giving me a funky output. The first two values in the data field as shown below are displayed as
39980 (instead of 06/16/2009)
39993 (instead of 06/29/2009)

The rest of the date values are displayed correctly!
ballardw
Super User
> Peter,
> Thanks for the help.When I use option mixed =yes,
> the date filed is giving me a funky output. The
> first two values in the data field as shown below
> are displayed as
> 39980 (instead of 06/16/2009)
> 39993 (instead of 06/29/2009)
>
> The rest of the date values are displayed correctly!

Apply a SAS date FORMAT to the variable, MMDDYY10. would work, to display as human useable date. Those values are the SAS day offset values it uses for numerical purposes. Notice that they differ by 13 matching the date difference.
ChrisNZ
Tourmaline | Level 20
Note that 39980 is 17JUN2069, not 16JUN2009.

The reason would be that excel counts days from 1/1/1900, whereas sas counts them from 1/1/1960, so the excel value has 60 more years into it. Not sure how you got to read 39980 in the 1st place, or why there is still a day difference, but now that you know the rule, you should be able to corrrect the date using function intnx.

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
  • 4 replies
  • 773 views
  • 0 likes
  • 4 in conversation