fix length of imported date variable

[jbrock]

Hello,

I am importing a dataset containing two date variables; below is how they look after import when I run a proc contents:

The first variable, death_date is reading in as a length of 2 because many of the values in the dataset are 'NA'.   I'd like to work with death_date for my analysis.  How can I get it to read in as it's full length?    I've tried specifying a length, format and informat but I think I'm using it in the wrong place.   I included my code below.         I am using SAS 9.4 TS15M.

Thanks!

death_date

Char

2

$2.

$2.

 

spec_col_date

Char

9

$9.

$9.

data work.allconfirmed;
length death_date $9.;
format death_date $9.;
informat death_date $9.;
set work.murphy;
where disease_status in ('Confirmed'); 
run;

[Patrick]

"I am importing a dataset containing two date variables"

If you mean by "importing" using the set statement set work.murphy; then the values with length of two would already be in the source table. Increasing the lengths won't give you better data.

You need to go to the place in your code where the column death_date gets created and fix things there.

If "importing" means reading data from an external file then please share the code with us.

[jbrock]

Hi Patrick,


Thanks for your response. The code I included comes after the import; I am using the import wizard to pull in my dataset which contains death_date. I think because the first value for death_date that appears in the dataset is 'NA', rather than an actual date (for instance '4/20/2020'), the variable coming in has a length of 2 instead of 9.


How can I specify the the length should be 9 even though the first several values are 'NA'?


Thanks

[Patrick]

Which import wizard? SAS EG, SAS Studio, ...?  The SAS EG wizard for example lets you change informats and lengths - just click a few times on the attribute you want to modify (you probably want to change the width of the informat and the length).

Alternatively use the code the wizard generates and change things there (you will also need to change the pathname so it points to the actual file).

[ballardw · Accepted solution]

How did you read the data in the first place? It sounds like you relied on proc import and have discovered that letting a procedure guess may not be the best method.

If you used proc import and the file was something text like CSV or tab delimited you might retry by specifying the option GUESSINGROWS=MAX; That would have the procedure examine more rows of the data before setting a variable type and informat.

Best would be to use a method that controls the read to get the values you actually want. Which in the case of dates would be a date informat and format. Again if you import a text file Proc Import will generate data step code that you could copy from the log and paste into the editor to modify as needed, such as specifying informats.

Once the value has been read as $2 it only has 2 characters in the values and nothing you do except re-read the raw data will get the rest of the value.

[jbrock]

Hello,

The GUESSINGROWS option worked perfectly.  You are correct in that I used proc import to pull in a .csv file.  

Your suggestion was very helpful- thank you!

[ballardw]

A hint for the future if you are going to have to read multiple files with the same structure.

Proc import will create data step code that appears in the log. You can copy that code to the editor and clean it up a bit like removing line numbers and such. Then examine all of the INFORMAT statements. If you are reading character values and may need to combine sets together later you might consider making the lengths longer. Or if you have variables that may not always be populated but you know the characteristics you would like then set an appropriate informat. Dates hopefully you know what they should look like and can assign the appropriate format.

   You can remove most of the FORMAT statements. I typically leave the dates unless I want a different appearance. SAS will default to the same date format as the informat.

I generally make sure things like account numbers, zip codes, phone numbers and such are character variables. If you don't intend to do arithmetic with it the variable should probably be character.

Save the code.

[jbrock]

?Thank you! That input is very helpful. I did that and now the program works- much appreciated.