Hello! Im pretty new to SAS, so sorry if i said something stupid XD.
My idea starts using a txt with this info:
ENTI|CIRBE|OPE|FIN|TPR|SIT|CONT|SEC|ACTE|PR|IMP-RIDISPU|IMP-RIDISPO|A|1|2|3|4|5|6|7|8|
9999|00000000000000000009999999|0000000000000000000000000000099999|K12|V44|I22|0020| | |01|000000000000063.12|000000000000000.00|N|0|0|0|0|0|0|0|0|
The number of colums might change between executions (now they go to 8, but they might go to 30, as long as the max lenght of the document is 3000).
What i want to do is:
1- Read the first line.
2- Create X number of colums, using the info obtained on the first line to label these colums.
3- After this colums have been created, write the rest of the document on this colums.
The final result should be something like this:
I have done loads before, but always with predefine labels and number of colums, so im completely lost about doing everything variable.
For now, i have make this:
data cabeceras (compress=binary);
infile datos truncover RECFM=V lrecl=3000 OBS=1;
input
LINEA $3000.
;
run;
data countit;
set cabeceras;
total = countw(LINEA,'|');
run;
I put the info of the first line on LINEA, and count the number of '|', so i now have the number of colums. And.... thats it. I have no idea about how to continue... Any clues?
Greetings.
Just tried it:
********************************************************************************
%let sas = eyrg0004a_fich1;
%let r_sas = /SASDATOS/MIS/Normativo/CIRBE;
%let r_txt = /SASDATOS/MIS/datos;
%let txt = salidak90.txt;
libname lib "&r_sas";
%macro carga;
%if %sysfunc(fileexist(&r_txt/&txt)) %then %do;
%sysexec fuser -k &r_sas/&sas..sas7bdat;
%put --------------------------------------------------------------------------;
%put - EL FICHERO &r_txt/&txt SI EXISTE;
%put --------------------------------------------------------------------------;
filename datos "&r_txt/&txt";
data lib.&sas (compress=binary);
proc import datafile=datos dbms=dlm out=work.test replace;
delimiter=”|”;
getnames=yes;
guessingrows=10;
run;
*****************************************************************************
* Asignamos permisos y grupo a la tabla de salida *
*****************************************************************************;
%put Asignamos permisos a la tabla;
%sysexec cd &r_sas;
%sysexec chmod 664 &r_sas./&sas..sas7bdat;
%sysexec chgrp sasmis &r_sas./&sas..sas7bdat;
%end;
%else %do;
%put --------------------------------------------------------------------------;
%put - EL FICHERO &r_txt/&txt NO EXISTE;
%put - No se ha cargado la tabla;
%put --------------------------------------------------------------------------;
%end;
%mend carga;
%carga;
After exec, i get this log:
22: LINE and COLUMN cannot be determined.
NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 22-322: Expecting ;.
200: LINE and COLUMN cannot be determined.
NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred.
ERROR 200-322: The symbol is not recognized and will be ignored.
NOTE: The previous statement has been deleted.
NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to
WORK.PARMS.PARMS.SLIST.
Name ENTI|CIRBE|OPE|FIN|TPR|SIT|CONT|SEC|ACTE|PR|IMP-RIDISPU|IMP-RIDISPO|A|1|2|3|4|5|6|7|8|
truncated to ENTI_CIRBE_OPE_FIN_TPR_SIT_CONT_.
Problems were detected with provided names. See LOG.
48 /**********************************************************************
49 * PRODUCT: SAS
50 * VERSION: 9.4
51 * CREATOR: External File Interface
52 * DATE: 07APR21
53 * DESC: Generated SAS Datastep Code
54 * TEMPLATE SOURCE: (None Specified.)
55 ***********************************************************************/
56 data WORK.TEST ;
57 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
58 infile DATOS delimiter = 'Â' MISSOVER DSD firstobs=2 ;
59 informat ENTI_CIRBE_OPE_FIN_TPR_SIT_CONT_ $152. ;
60 format ENTI_CIRBE_OPE_FIN_TPR_SIT_CONT_ $152. ;
61 input
62 ENTI_CIRBE_OPE_FIN_TPR_SIT_CONT_ $
63 ;
64 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
65 run;NOTE: The infile DATOS is:
Filename=/SASDATOS/MIS/datos/salidak90.txt,
Owner Name=xe22674,Group Name=sas,
Access Permission=-rw-r--r--,
Last Modified=07Apr2021:13:33:47,
File Size (bytes)=31715NOTE: 10 records were read from the infile DATOS.
The minimum record length was 1760.
The maximum record length was 3001.
I ended up getting the sas7bat archive, but i cant load it. I get that the headers got cut, but i dont get the other errors.
You have incorrect quotes here:
delimiter=”|”;
(curly quotes caused by word processor or similar)
and you seem to have an encoding problem with the delimiter; the correct delimiter is hex 7C, not some funny UTF-encoded thing. You can use
delimiter='7C'x;
This works for me:
data _null_;
infile datalines truncover;
file '/folders/myfolders/deivid64.txt';
input string $100.;
put string;
datalines;
ENTI|CIRBE|OPE|FIN|TPR|SIT|CONT|SEC|ACTE|PR|IMP-RIDISPU|IMP-RIDISPO|A|1|2|3|4|5|6|7|8|
9999|00000000000000000009999999|0000000000000000000000000000099999|K12|V44|I22|0020| | |01|000000000000063.12|000000000000000.00|N|0|0|0|0|0|0|0|0|
;
proc import
datafile="/folders/myfolders/deivid64.txt"
out=deivid
dbms=dlm
replace
;
delimiter="|";
run;
Bottom line: don't put something into a macro before you have made sure it works without macro stuff.
The delimiter 7C worked perfectly! I was able to execute and the log now dont have errors:
61 /**********************************************************************
62 * PRODUCT: SAS
63 * VERSION: 9.4
64 * CREATOR: External File Interface
65 * DATE: 07APR21
66 * DESC: Generated SAS Datastep Code
67 * TEMPLATE SOURCE: (None Specified.)
68 ***********************************************************************/
69 data WORK.EYRG0004A ;
70 %let _EFIERR_ = 0; /* set the ERROR detection macro variable */
71 infile DATOS delimiter = '|' MISSOVER DSD firstobs=2 ;
72 informat ENTI best32. ;
73 informat CIRBE best32. ;
74 informat OPE best32. ;
75 informat FIN $3. ;
76 informat TPR $3. ;
77 informat SIT $3. ;
78 informat CONT best32. ;
79 informat SEC $1. ;
80 informat ACTE $1. ;
81 informat PR best32. ;
82 informat IMP_RIDISPU best32. ;
83 informat IMP_RIDISPO best32. ;
84 informat A $1. ;
5 The SAS System 16:21 Wednesday, April 7, 2021
85 informat _1 best32. ;
86 informat _2 best32. ;
87 informat _3 best32. ;
88 informat _4 best32. ;
89 informat _5 best32. ;
90 informat _6 best32. ;
91 informat _7 best32. ;
92 informat _8 best32. ;
93 informat VAR22 $1. ;
94 format ENTI best12. ;
95 format CIRBE best12. ;
96 format OPE best12. ;
97 format FIN $3. ;
98 format TPR $3. ;
99 format SIT $3. ;
100 format CONT best12. ;
101 format SEC $1. ;
102 format ACTE $1. ;
103 format PR best12. ;
104 format IMP_RIDISPU best12. ;
105 format IMP_RIDISPO best12. ;
106 format A $1. ;
107 format _1 best12. ;
108 format _2 best12. ;
109 format _3 best12. ;
110 format _4 best12. ;
111 format _5 best12. ;
112 format _6 best12. ;
113 format _7 best12. ;
114 format _8 best12. ;
115 format VAR22 $1. ;
116 input
117 ENTI
118 CIRBE
119 OPE
120 FIN $
121 TPR $
122 SIT $
123 CONT
124 SEC $
125 ACTE $
126 PR
127 IMP_RIDISPU
128 IMP_RIDISPO
129 A $
130 _1
131 _2
132 _3
133 _4
134 _5
135 _6
136 _7
137 _8
138 VAR22 $
139 ;
140 if _ERROR_ then call symputx('_EFIERR_',1); /* set ERROR detection macro variable */
141 run;
6 The SAS System 16:21 Wednesday, April 7, 2021
NOTE: The infile DATOS is:
Filename=/SASDATOS/MIS/datos/salidak90.txt,
Owner Name=xe22674,Group Name=sas,
Access Permission=-rw-r--r--,
Last Modified=07Apr2021:13:33:47,
File Size (bytes)=31715
NOTE: 10 records were read from the infile DATOS.
The minimum record length was 1760.
The maximum record length was 3001.
NOTE: The data set WORK.EYRG0004A has 10 observations and 22 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
user cpu time 0.01 seconds
system cpu time 0.00 seconds
memory 9873.37k
OS Memory 17864.00k
Timestamp 04/07/2021 04:21:58 PM
Step Count 3 Switch Count 0
Page Faults 0
Page Reclaims 247
Page Swaps 0
Voluntary Context Switches 2
Involuntary Context Switches 0
Block Input Operations 0
Block Output Operations 0
10 rows created in WORK.EYRG0004A from DATOS.
I still cant open the sas7bat, but is probably just me moving the wrong variable after Import. I will check after launch. Thanks!
Edit: Yep, just change a few names and now i can 😛
Strongly suggest using GUESSINGROWS=MAX or a much larger number than 10.
If you have data such that the first 10 rows of your data have missing values for a "column" or have values that are character and shorter than appears later in your data then values get truncated to the length that appears in the first 10 rows.
A larger value for the Guessingrows option means more lines are examined of the file before setting properties like variable length, type and format.
Just added it for safe. Thanks!
If the only thing that varies is the number of columns at the end then that should be easy to do.
Let's convert your example two lines into an actual file we use for testing.
filename sample temp;
options parmcards=sample;
parmcards;
ENTI|CIRBE|OPE|FIN|TPR|SIT|CONT|SEC|ACTE|PR|IMP-RIDISPU|IMP-RIDISPO|A|1|2|3|4|5|6|7|8|
9999|00000000000000000009999999|0000000000000000000000000000099999|K12|V44|I22|0020| | |01|000000000000063.12|000000000000000.00|N|0|0|0|0|0|0|0|0|
;
Now let's read the first line and pull that 8 from the end of it and put it into a macro variable.
data n_vars ;
infile sample obs=1;
input ;
n_vars = scan(trim(_infile_),-1,'|');
call symputx('n_vars',n_vars);
run;
Now let's write a data step to read the file. We will use the macro variable N_VARS to set the upperbound on how many of those variables to read from the end. I just guessed how you want to define the variables from how the one example record looks like. Notice how I used valid SAS names for the variables instead of trying to use the column headers exactly. I replaced the hyphens with underscores. I named those variables at the end of the lines as VAR1, VAR2, ... since you cannot start a variable name with a digit.
data want ;
infile sample dsd dlm='|' truncover firstobs=2;
length
ENTI $10
CIRBE $20
OPE $30
FIN $8
TPR $8
SIT $8
CONT $10
SEC $8
ACTE $8
PR $8
IMP_RIDISPU 8
IMP_RIDISPO 8
A $8
var1-var&n_vars 8
;
input enti -- var&n_vars ;
run;
Writing your own data step gives you complete control over how the variables are defined. It also insures that the variables are defined the same every time, even when sometimes the variables are empty in a particular version of the text file. Also notice how much simpler the code is when you write your own data step instead of the gibberish that PROC IMPORT generates.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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: