topic Re: Importing TSV file to SAS _date of birth issue in SAS Programming

Importing TSV file to SAS _date of birth issue

ali_far — Sun, 21 Jul 2019 11:37:15 GMT

Hi all,

I have a question about importing TSV file to sas.

the datafile has got date of birth variable ( yy/mm/dd). However, we importing the data, this variable is missing.

data want;

%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
Filename Alii 'E:\want.tsv' encoding='utf8';

infile Alii delimiter='09'x MISSOVER DSD lrecl=32767 firstobs=2 ;informat male_head_birth anydtdtm40. ;

/*for all variables*/

informat ID_date_birth anydtdtm40. ;

format ID_date_birth datetime. ;

ID_date_birth;

if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
run;

Thanks

Re: Importing TSV file to SAS _date of birth issue

Kurt_Bremser — Sun, 21 Jul 2019 11:53:35 GMT

If you already know the date format, DO NOT use an "any" format. Especially not the one for datetimes when you want to read dates.

For a date in YY/MM/DD format, use the YYMMDD8. informat.

BTW, who in his right mind uses a two-digit year anymore? The Y2K scare should have been enough to convince even a very unintelligent person to use FOUR digits! And you will have centenarians that are considered to be toddlers.

Re: Importing TSV file to SAS _date of birth issue

ali_far — Sun, 21 Jul 2019 12:11:37 GMT

Hi,
Thanks for your reply.
I dont have the format of the date from the TSV file( I should not have written that format).However, in the similar datafile I have ( I imported them from TSV to SAS with the similar code) the format of the variable (numeric) is DATETIME16.

If I dont use any format then the variable will only contain the year!

Re: Importing TSV file to SAS _date of birth issue

Tom — Sun, 21 Jul 2019 15:06:02 GMT

Show an example of a couple of the lines from the file you are reading. Use the Insert Code icon (looks like {i}) on the menu bar to get a pop-up window where you can paste in the lines. THis will preserve the formatting.

If you want to read the dates as actual DATE values instead of DATETIME values then use an INFORMAT that will create a date value (like YYMMDD.) and use a format that works on date values (like YYMMDD10.).

Your program does not have any INPUT statement. So all of the variables will be missing.

Re: Importing TSV file to SAS _date of birth issue

Reeza — Sun, 21 Jul 2019 20:13:44 GMT

1. FILENAME statement needs to be before the PROC if you're using one (orange)

2. 99% of the time you want TRUNCOVER instead of MISSOVER (purple)

3. I have no idea what that line with just a variable name is supposed to be doing (green)

4. You have no INPUT statement to actually read any variables from a file. You have to read all variables and then you can choose what to keep and what to leave via a DROP or KEEP statement. (Red).

Filename Alii 'E:\want.tsv' encoding='utf8';
 
data want;

%let _EFIERR_ = 0; /* set the ERROR detection macro variable */


infile Alii delimiter='09'x TRUNCOVER DSD lrecl=32767 firstobs=2 ;

informat male_head_birth anydtdtm40. ;
 informat ID_date_birth anydtdtm40. ;
format ID_date_birth datetime. ;


ID_date_birth;

INPUT .... list of variables go here.....;
 
if _ERROR_ then call symputx('_EFIERR_',1);  /* set ERROR detection macro variable */
run;

@ali_far wrote:

Hi all,

I have a question about importing TSV file to sas.

the datafile has got date of birth variable ( yy/mm/dd). However, we importing the data, this variable is missing.

data want;

%let _EFIERR_ = 0; /* set the ERROR detection macro variable */
Filename Alii 'E:\want.tsv' encoding='utf8';

infile Alii delimiter='09'x MISSOVER DSD lrecl=32767 firstobs=2 ;informat male_head_birth anydtdtm40. ;

/*for all variables*/

informat ID_date_birth anydtdtm40. ;

format ID_date_birth datetime. ;

ID_date_birth;

if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
run;

Thanks

Re: Importing TSV file to SAS _date of birth issue

ali_far — Mon, 22 Jul 2019 02:54:07 GMT

Thanks @Reeza for sharing your thoughts.
Actually, I had tried TRUNCOVER before but It did not work.
In terms of Input statement, Of course, it is must and I had it in the code before entering the range of variables but I forgot to write it here!

One thing I found is that when I import the variable individually (only one id_birth) it looks fine. but when I even import two variables ( two id_birth ) one of them will be completely missing. and when I run the whole code all id_birth variables are missing.
Do you have any idea about it?

Re: Importing TSV file to SAS _date of birth issue

Tom — Mon, 22 Jul 2019 03:42:37 GMT

One guess is that your delimiter is not what you think it is. The other is that your INPUT statement is wrong. For reading from a delimited file you just want to list the variables. If you decide you have to include an informat specification in the INPUT statement then make sure the prefix it with a colon to prevent SAS from reading a fixed number of characters and ignoring the delimiters.
To get real answers show the log of the code you actually ran.

Re: Importing TSV file to SAS _date of birth issue

Kurt_Bremser — Mon, 22 Jul 2019 06:21:57 GMT

Provide an example of the data, as @Tom already asked you to do.

Help us to help you.

Re: Importing TSV file to SAS _date of birth issue

ali_far — Mon, 22 Jul 2019 12:55:16 GMT

Hi,
Here is the log of the code which has been run.
No errors.! but the Id_date_birth are missing. Again if I just import these variables they will be imported properly.

660 Filename Alii'D:\want.tsv' encoding='utf8';
661
662 data want;
663 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
664 infile Alii delimiter='09'x TRUNCOVER DSD lrecl=32767 firstobs=2 ;
665
666 informat panel_year best32. ;
667 informat projection_factor best32. ;
668 informat projection_factor_magnet best32. ;
669 informat Id_Date_birth_1 anydtdtm40. ;
670 informat Id_Date_birth_2 anydtdtm40. ;
671 informat dma_code best32. ;
672
673 format panel_year best12. ;
674 format projection_factor best12. ;
675 format projection_factor_magnet best12. ;
676 format Id_Date_birth_1 datetime. ;
677 format Id_Date_birth_2 datetime. ;
678 format dma_code best12. ;
679
680 input
681 panel_year
682 projection_factor
683 projection_factor_magnet
684 Id_Date_birth_1
685 Id_Date_birth_2
686 dma_code
687
688 ;
689 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
690 run;

NOTE: The infile Alii is:
Filename=D:\want.tsv,
RECFM=V,LRECL=131068,
File Size (bytes)=10131988,
Last Modified=11Jul2018:23:45:43,
Create Time=19Jul2019:16:20:57

NOTE: 63150 records were read from the infile Alii.
The minimum record length was 130.
The maximum record length was 220.
NOTE: The data set WORK.want has 63150 observations and 6 variables.
NOTE: DATA statement used (Total process time):
real time 0.15 seconds
cpu time 0.14 seconds

Re: Importing TSV file to SAS _date of birth issue

Tom — Mon, 22 Jul 2019 13:15:16 GMT

The code looks fine (other than the unneeded informats and formats for the numeric variables).

There is no variable named Id_date_birth mentioned in the data step however. There are two other variables instead.

Id_date_birth_1 and Id_date_birth_2

Show one of the lines form the file that you think has a valid date that the code is not reading.

Re: Importing TSV file to SAS _date of birth issue

ballardw — Mon, 22 Jul 2019 15:13:17 GMT

@ali_far wrote:
Hi,
Here is the log of the code which has been run.
No errors.! but the Id_date_birth are missing. Again if I just import these variables they will be imported properly.

660 Filename Alii'D:\want.tsv' encoding='utf8';
661
662 data want;
663 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
664 infile Alii delimiter='09'x TRUNCOVER DSD lrecl=32767 firstobs=2 ;
665
666 informat panel_year best32. ;
667 informat projection_factor best32. ;
668 informat projection_factor_magnet best32. ;
669 informat Id_Date_birth_1 anydtdtm40. ;
670 informat Id_Date_birth_2 anydtdtm40. ;

WHICH Id_date_birth is missing. The proc import codes that you have two variables that could be the one you are talking about.

And SAS would not be using anydtdtm40. as an informat if the value did not appear to contain time values along with the date.

If, as you say, the date portion is formatted as yy/mm/dd you can change the generated code to use a different informat and format.

Probably

informat Id_Date_birth_1 Id_Date_birth_2 yymmdd10. ;

would work just fine to read PART of the date from a longer string.

If your value is a 2-digit year you may need yymmdd8.

Use a matching format assignment.

But ACTUAL examples of the file really help getting code that works.