topic Date format for fixed columns input infile in SAS Procedures

Date format for fixed columns input infile

Iske — Fri, 10 Sep 2021 19:19:05 GMT

I am trying to read a txt file using infile with fixed columns. The data is currently reading correctly, but still needs to be formatted. F1 needs to be a character, F2 Numeric, F3 Date YYMMDD10 and F4 Numeric. How do I modify F3 to so it can be read as a date type?

DATA PDP_DATA;
infile "/data/Sample_data.txt"
firstobs=2;
input
F1 1-1
F2 $ 2-10
F3 $ 11-25
F4 $ 26-40;
RUN;

Re: Date format for fixed columns input infile

Reeza — Fri, 10 Sep 2021 20:19:51 GMT

DATA PDP_DATA;
infile "/data/Sample_data.txt"
firstobs=2;
informat f1 $10.;
informat f2 8.;
informat F3 yymmdd10. f4 8.;
format f3 yymmddd10.;
input
F1  1-1
F2  2-10
F3  11-25
F4  26-40;
RUN;

1. Specify an INFORMAT for each variable that aligns with how the variable is currently displayed (ie yymmdd or dollar).

2. Remove the $ which tells SAS to read the variables as character variables.

If this doesn't work, post a few lines of the log - if you can share the data.

@Iske wrote:

I am trying to read a txt file using infile with fixed columns. The data is currently reading correctly, but still needs to be formatted. F1 needs to be a character, F2 Numeric, F3 Date YYMMDD10 and F4 Numeric. How do I modify F3 to so it can be read as a date type?

DATA PDP_DATA;
infile "/data/Sample_data.txt"
firstobs=2;
input
F1 1-1
F2 $ 2-10
F3 $ 11-25
F4 $ 26-40;
RUN;

Re: Date format for fixed columns input infile

data_null__ — Fri, 10 Sep 2021 20:34:17 GMT

@Reeza wrote:
DATA PDP_DATA;
infile "/data/Sample_data.txt"
firstobs=2;
informat f1 $10.;
informat f2 8.;
informat F3 yymmdd10. f4 8.;
format f3 yymmddd10.;
input
F1  1-1
F2  2-10
F3  11-25
F4  26-40;
RUN;
1. Specify an INFORMAT for each variable that aligns with how the variable is currently displayed (ie yymmdd or dollar).

2. Remove the $ which tells SAS to read the variables as character variables.

If this doesn't work, post a few lines of the log - if you can share the data.

@Iske wrote:

I am trying to read a txt file using infile with fixed columns. The data is currently reading correctly, but still needs to be formatted. F1 needs to be a character, F2 Numeric, F3 Date YYMMDD10 and F4 Numeric. How do I modify F3 to so it can be read as a date type?

DATA PDP_DATA;
infile "/data/Sample_data.txt"
firstobs=2;
input
F1 1-1
F2 $ 2-10
F3 $ 11-25
F4 $ 26-40;
RUN;

I think this is relevant.

Re: Date format for fixed columns input infile

Iske — Fri, 10 Sep 2021 20:39:06 GMT

Is there a way I could only format the date and leave the rest as is? The actual data I am working with has a total of 171 columns.

I have a dictionary of what they should be.

Name	Type	Length	Format	Informat	Label
F1	Numeric	8	BEST1.	BEST1.	
F2	Character	9	$CHAR9.	$CHAR9.	
F3	Character	15	$CHAR15.	$CHAR15.	
F4	Character	15	$CHAR15.	$CHAR15.	
F5	Numeric	8	BEST18.	BEST18.	
F6	Character	3	$CHAR3.	$CHAR3.	
F7	Numeric	8	BEST1.	BEST1.	
F8	Character	25	$CHAR25.	$CHAR25.	
F9	Character	15	$CHAR15.	$CHAR15.	
F10	Character	1	$CHAR1.	$CHAR1.	
F11	Character	1	$CHAR1.	$CHAR1.	
F12	Date	8	YYMMDD10.	YYMMDD10.	
F13	Numeric	8	BEST1.	BEST1.	
F14	Character	1	$CHAR1.	$CHAR1.	
F15	Numeric	8	BEST1.	BEST1.	
F16	Character	1	$CHAR1.	$CHAR1.	
F17	Character	18	$CHAR18.	$CHAR18.	
F18	Numeric	8	BEST9.	BEST9.	
F19	Character	25	$CHAR25.	$CHAR25.	
F20	Character	15	$CHAR15.	$CHAR15.	
F21	Character	20	$CHAR20.	$CHAR20.	
F22	Character	2	$CHAR2.	$CHAR2.	
F23	Numeric	8	BEST5.	BEST5.	
F24	Numeric	8	BEST4.	BEST4.	
F25	Character	2	$CHAR2.	$CHAR2.	
F26	Character	4	$CHAR4.	$CHAR4.	
F27	Numeric	8	BEST10.	BEST10.	
F28	Character	1	$CHAR1.	$CHAR1.	
F29	Numeric	8	BEST1.	BEST1.	
F30	Numeric	8	BEST18.	BEST18.	
F31	Numeric	8	BEST7.	BEST7.	
F32	Numeric	8	BEST7.	BEST7.	
F33	Numeric	8	BEST7.	BEST7.	
F34	Numeric	8	BEST7.	BEST7.	
F35	Character	10	$CHAR10.	$CHAR10.	
F36	Numeric	8	BEST7.	BEST7.	
F37	Numeric	8	BEST5.	BEST5.	
F38	Numeric	8	BEST5.	BEST5.	
F39	Numeric	8	BEST5.	BEST5.	
F40	Numeric	8	BEST5.	BEST5.	
F41	Character	6	$CHAR6.	$CHAR6.	
F42	Character	6	$CHAR6.	$CHAR6.	
F43	Numeric	8	BEST7.	BEST7.	
F44	Numeric	8	BEST7.	BEST7.	
F45	Character	10	$CHAR10.	$CHAR10.	
F46	Character	10	$CHAR10.	$CHAR10.	
F47	Numeric	8	BEST7.	BEST7.	
F48	Character	6	$CHAR6.	$CHAR6.	
F49	Character	10	$CHAR10.	$CHAR10.	
F50	Character	15	$CHAR15.	$CHAR15.	
F51	Character	2	$CHAR2.	$CHAR2.	
F52	Character	3	$CHAR3.	$CHAR3.	
F53	Numeric	8	BEST7.	BEST7.	
F54	Numeric	8	BEST7.	BEST7.	
F55	Character	1	$CHAR1.	$CHAR1.	
F56	Character	10	$CHAR10.	$CHAR10.	
F57	Character	18	$CHAR18.	$CHAR18.	
F58	Character	1	$CHAR1.	$CHAR1.	
F59	Numeric	8	BEST1.	BEST1.	
F60	Character	5	$CHAR5.	$CHAR5.	
F61	Character	20	$CHAR20.	$CHAR20.	
F62	Numeric	8	BEST3.	BEST3.	
F63	Numeric	8	BEST3.	BEST3.	
F64	Character	1	$CHAR1.	$CHAR1.	
F65	Numeric	8	BEST3.	BEST3.	
F66	Numeric	8	BEST1.	BEST1.	
F67	Numeric	8	BEST8.	BEST8.	
F68	Character	1	$CHAR1.	$CHAR1.	
F69	Numeric	8	BEST7.	BEST7.	
F70	Numeric	8	BEST7.	BEST7.	
F71	Character	6	$CHAR6.	$CHAR6.	
F72	Character	10	$CHAR10.	$CHAR10.	
F73	Character	15	$CHAR15.	$CHAR15.	
F74	Numeric	8	BEST10.	BEST10.	
F75	Character	20	$CHAR20.	$CHAR20.	
F76	Character	3	$CHAR3.	$CHAR3.	
F77	Character	1	$CHAR1.	$CHAR1.	
F78	Character	1	$CHAR1.	$CHAR1.	
F79	Numeric	8	BEST7.	BEST7.	
F80	Numeric	8	BEST7.	BEST7.	
F81	Character	6	$CHAR6.	$CHAR6.	
F82	Character	10	$CHAR10.	$CHAR10.	
F83	Character	15	$CHAR15.	$CHAR15.	
F84	Numeric	8	BEST10.	BEST10.	
F85	Character	20	$CHAR20.	$CHAR20.	
F86	Character	3	$CHAR3.	$CHAR3.	
F87	Character	1	$CHAR1.	$CHAR1.	
F88	Character	1	$CHAR1.	$CHAR1.	
F89	Numeric	8	BEST7.	BEST7.	
F90	Numeric	8	BEST7.	BEST7.	
F91	Character	6	$CHAR6.	$CHAR6.	
F92	Character	10	$CHAR10.	$CHAR10.	
F93	Character	15	$CHAR15.	$CHAR15.	
F94	Numeric	8	BEST10.	BEST10.	
F95	Character	20	$CHAR20.	$CHAR20.	
F96	Character	3	$CHAR3.	$CHAR3.	
F97	Character	1	$CHAR1.	$CHAR1.	
F98	Character	6	$CHAR6.	$CHAR6.	
F99	Character	6	$CHAR6.	$CHAR6.	
F100	Character	6	$CHAR6.	$CHAR6.	
F101	Character	6	$CHAR6.	$CHAR6.	
F102	Character	6	$CHAR6.	$CHAR6.	
F103	Character	6	$CHAR6.	$CHAR6.	
F104	Character	6	$CHAR6.	$CHAR6.	
F105	Character	6	$CHAR6.	$CHAR6.	
F106	Character	6	$CHAR6.	$CHAR6.	
F107	Character	6	$CHAR6.	$CHAR6.	
F108	Character	3	$CHAR3.	$CHAR3.	
F109	Character	3	$CHAR3.	$CHAR3.	
F110	Character	3	$CHAR3.	$CHAR3.	
F111	Character	3	$CHAR3.	$CHAR3.	
F112	Character	3	$CHAR3.	$CHAR3.	
F113	Character	3	$CHAR3.	$CHAR3.	
F114	Numeric	8	BEST5.	BEST5.	
F115	Numeric	8	BEST5.	BEST5.	
F116	Character	1	$CHAR1.	$CHAR1.	
F117	Character	1	$CHAR1.	$CHAR1.	
F118	Character	1	$CHAR1.	$CHAR1.	
F119	Character	1	$CHAR1.	$CHAR1.	
F120	Character	1	$CHAR1.	$CHAR1.	
F121	Character	1	$CHAR1.	$CHAR1.	
F122	Character	1	$CHAR1.	$CHAR1.	
F123	Numeric	8	BEST7.	BEST7.	
F124	Numeric	8	BEST7.	BEST7.	
F125	Character	5	$CHAR5.	$CHAR5.	
F126	Character	10	$CHAR10.	$CHAR10.	
F127	Numeric	8	BEST7.	BEST7.	
F128	Numeric	8	BEST7.	BEST7.	
F129	Character	4	$CHAR4.	$CHAR4.	
F130	Date	8	YYMMDD10.	YYMMDD10.	
F131	Date	8	YYMMDD10.	YYMMDD10.	
F132	Numeric	8	BEST4.	BEST4.	
F133	Character	1	$CHAR1.	$CHAR1.	
F134	Character	1	$CHAR1.	$CHAR1.	
F135	Character	10	$CHAR10.	$CHAR10.	
F136	Character	10	$CHAR10.	$CHAR10.	
F137	Numeric	8	BEST8.	BEST8.	
F138	Character	1	$CHAR1.	$CHAR1.	
F139	Character	1	$CHAR1.	$CHAR1.	
F140	Character	1	$CHAR1.	$CHAR1.	
F141	Character	1	$CHAR1.	$CHAR1.	
F142	Numeric	8	BEST8.	BEST8.	
F143	Numeric	8	BEST8.	BEST8.	
F144	Character	1	$CHAR1.	$CHAR1.	
F145	Date	8	YYMMDD10.	YYMMDD10.	
F146	Numeric	8	BEST8.	BEST8.	
F147	Character	8	$CHAR8.	$CHAR8.	
F148	Character	4	$CHAR4.	$CHAR4.	
F149	Date	8	YYMMDD10.	YYMMDD10.	
F150	Character	40	$CHAR40.	$CHAR40.	
F151	Character	40	$CHAR40.	$CHAR40.	
F152	Character	19	$CHAR19.	$CHAR19.	
F153	Character	2	$CHAR2.	$CHAR2.	
F154	Numeric	8	BEST11.	BEST11.	
F155	Character	4	$CHAR4.	$CHAR4.	
F156	Date	8	YYMMDD10.	YYMMDD10.	
F157	Character	1	$CHAR1.	$CHAR1.	
F158	Character	20	$CHAR20.	$CHAR20.	
F159	Character	30	$CHAR30.	$CHAR30.	
F160	Character	30	$CHAR30.	$CHAR30.	
F161	Character	30	$CHAR30.	$CHAR30.	
F162	Numeric	8	BEST7.	BEST7.	
F163	Numeric	8	BEST7.	BEST7.	
F164	Character	10	$CHAR10.	$CHAR10.	
F165	Numeric	8	BEST2.	BEST2.	
F166	Character	1	$CHAR1.	$CHAR1.	
F167	Character	50	$CHAR50.	$CHAR50.	
F168	Character	1	$CHAR1.	$CHAR1.	
F169	Numeric	8	BEST8.	BEST8.	
F170	Character	1	$CHAR1.	$CHAR1.	
F171	Character	239	$CHAR239.	$CHAR239.

Re: Date format for fixed columns input infile

Tom — Fri, 10 Sep 2021 22:17:55 GMT

If you want to use a special INFORMAT with fixed column text file then use formatted mode input, not column mode. You can use @ cursor movement command to position the cursor at the first column that has the data and the width of the INFORMAT to specify how many columns to read. You might need to adjust because 10 character date string would not completely fill the 15 columns from 11 thru 25.

DATA PDP_DATA;
  infile "/data/Sample_data.txt" firstobs=2;
  input
    F1 1-1
    F2 $ 2-10
    @11 F3 YYMMDD10.
    F4 $ 26-40
  ;
  format f3 yymmdd10. ;
RUN;

Re: Date format for fixed columns input infile

data_null__ — Mon, 13 Sep 2021 18:53:28 GMT

You should have mentioned that you had a file with all the information to read the file.

data info;
   infile cards dsd dlm='09'x firstobs=2;
   input name:$32. type:$1. length:5. (format informat)(:$32.);
   cards;
Name	Type	Length	Format	Informat	Label
F1	Numeric	8	BEST1.	BEST1.	
F2	Character	9	$CHAR9.	$CHAR9.	
F3	Character	15	$CHAR15.	$CHAR15.	
F4	Character	15	$CHAR15.	$CHAR15.	
F5	Numeric	8	BEST18.	BEST18.	
F6	Character	3	$CHAR3.	$CHAR3.	
F7	Numeric	8	BEST1.	BEST1.	
F8	Character	25	$CHAR25.	$CHAR25.	
F9	Character	15	$CHAR15.	$CHAR15.	
F10	Character	1	$CHAR1.	$CHAR1.	
F11	Character	1	$CHAR1.	$CHAR1.	
F12	Date	8	YYMMDD10.	YYMMDD10.	
F13	Numeric	8	BEST1.	BEST1.	
F14	Character	1	$CHAR1.	$CHAR1.	
F15	Numeric	8	BEST1.	BEST1.	
F16	Character	1	$CHAR1.	$CHAR1.	
F17	Character	18	$CHAR18.	$CHAR18.	
F18	Numeric	8	BEST9.	BEST9.	
F19	Character	25	$CHAR25.	$CHAR25.	
F20	Character	15	$CHAR15.	$CHAR15.	
F21	Character	20	$CHAR20.	$CHAR20.	
F22	Character	2	$CHAR2.	$CHAR2.	
F23	Numeric	8	BEST5.	BEST5.	
F24	Numeric	8	BEST4.	BEST4.	
F25	Character	2	$CHAR2.	$CHAR2.	
F26	Character	4	$CHAR4.	$CHAR4.	
F27	Numeric	8	BEST10.	BEST10.	
F28	Character	1	$CHAR1.	$CHAR1.	
F29	Numeric	8	BEST1.	BEST1.	
F30	Numeric	8	BEST18.	BEST18.	
F31	Numeric	8	BEST7.	BEST7.	
F32	Numeric	8	BEST7.	BEST7.	
F33	Numeric	8	BEST7.	BEST7.	
F34	Numeric	8	BEST7.	BEST7.	
F35	Character	10	$CHAR10.	$CHAR10.	
F36	Numeric	8	BEST7.	BEST7.	
F37	Numeric	8	BEST5.	BEST5.	
F38	Numeric	8	BEST5.	BEST5.	
F39	Numeric	8	BEST5.	BEST5.	
F40	Numeric	8	BEST5.	BEST5.	
F41	Character	6	$CHAR6.	$CHAR6.	
F42	Character	6	$CHAR6.	$CHAR6.	
F43	Numeric	8	BEST7.	BEST7.	
;;;;
   run;
proc print;
   run; 
filename stmts temp;
data _null_;
   file stmts;
   set info;
   put 'Input ' name informat '@;';
   if format in:('YYMMDD') then put +3 'Format ' name format ';';
   run; 
    

data pop_data;
   infile "/data/Sample_data.txt";
   %include stmts;
   run;

Input F1 BEST1. @;
Input F2 $CHAR9. @;
Input F3 $CHAR15. @;
Input F4 $CHAR15. @;
Input F5 BEST18. @;
Input F6 $CHAR3. @;
Input F7 BEST1. @;
Input F8 $CHAR25. @;
Input F9 $CHAR15. @;
Input F10 $CHAR1. @;
Input F11 $CHAR1. @;
Input F12 YYMMDD10. @;
   Format F12 YYMMDD10. ;
Input F13 BEST1. @;
Input F14 $CHAR1. @;
Input F15 BEST1. @;
Input F16 $CHAR1. @;
Input F17 $CHAR18. @;