Create colums with info provided by .txt

[Deivid64]

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.

[Kurt_Bremser · Accepted solution]

Take a look at PROC IMPORT.

[Deivid64]

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)=31715

NOTE: 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.

[Kurt_Bremser]

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.

[Deivid64]

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 😛

[ballardw]

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.

[Deivid64]

Just added it for safe. Thanks!

[Tom]

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.