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
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.
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.
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
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.
@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.
Provide an example of the data, as @Tom already asked you to do.
Help us to help you.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: