Parsing column names and corresponding rows from a messy text file

[Tom]

That line is NOT using semi-colon between the name=value pairs.  It has some other character there. Is it a space? Or a tab?

[emssas]

Good catch, Tom.  It is a space.

[Kurt_Bremser]

Which means you have to change the delimiter in the INFILE statement accordingly.

[emssas]

Kurt,

I did that, but I still am not seeing any records in the output data.  How should I change the delimiter in the infile statement?

[Kurt_Bremser]

With the delimiter changed to a blank, my code works:

data fixerdata;
infile datalines dlm=' ' truncover obs=10;
length
    item $100
    name $5
    value $95
    var8001 $20
    var17 $30
    var11106 $2
    var54 $4
    var9713 $6
    var60 8
    var32 $4
    var120 $4
    var31 6.11
    var15 $4
   var8147 $100
   var8100 $15
   var55 $5
   var65 $10
;
format
   var60 e8601dt25.
;
input item @;
put _infile_;
do while (item ne " ");
    name = scan(item,1,"=");
    value = scan(item,2,"=");
    select (name);
        when ("8001") var8001 = value;
        when ("17") var17 = value;
        when ("11106") var11106 = value;
        when ("54") var54 = value;
        when ("9713") var9713 = value;
        when ("60") var60 = dhms(input(scan(value,1,"-"),yymmdd8.),0,0,input(scan(value,2,"-"),time12.));
        when ("32") var32 = value;
        when ("120") var120 = value;
        when ("31") var31 = value;
        when ("15") var15 = value;
        when ("8147") var8147 = value;
        when ("8100") var8100 = value;
        when ("55") var55 = value;
        when ("65") var65 = value;
        otherwise;
    end;
    input item @;
end;
drop item name value;
datalines;
15=USD 17=00004885118TRTR1.1.1 31=779.530000 32=100 54=1 55=LML 60=20210414-14:47:46 65= 120=USD 8001=EXEC_FEW 8100= 8147=Xxxxxxxx.Xxxxxxx@XXXX.XXX 9713=815215 11106=40
;

proc print data=fixerdata noobs;
run;

Result:

var8001	var17	var11106	var54	var9713	var60	var32	var120	var31	var15	var8147	var8100	var55	var65
EXEC_FEW	00004885118TRTR1.1.1	40	1	815215	2021-04-14T14:47:46	100	USD	779.530	USD	Xxxxxxxx.Xxxxxxx@XXXX.XXX	 	LML	 

[ErikLund_Jensen]

Hi @emssas 

I would suggest a different approach that is somewhat more complicated, but might save a lot of work later in the process. 

First step is to read the file into a SAS data set, that with record_no from the original file + an observation for each name-value pair in the input row. The result would be (using your test data):

Next step is to analyze the content and make a list of all variables with type (num or char) and max used length.

Third step is to use this information to generate a data step that reads the dataset from first step and recreates the original structure with onr observation per input row and the relevant variables in numerical order. Numeric variables are read as numbers and character variables have the proper length to hold the longest input value.

The reslut is almost as you want, only the variables are created as V1 - Vnnnn to avoid problems with SAS naming conventions, and they occur in numeric order regardless of the sequence (or lack of sequence) in the input rows.

And here the code:

* create test file;
data _null_;
	file "c:\temp\file.csv";
	input;
	put _infile_;
	cards4;
6=FIX.3.3; 5=1057; 25=8; 24=798; 55=RBCC_MID_PROD; 56=TOP_MUD_PROD; 33=N; 52=20210915-12:38:16.753; 
24=802; 5=1079; 25=8; 6=FIX.3.3; 55=RBCC_MID_PROD; 56=TOP_MUD_PROD; 33=N; 52=20210915-12:39:50.053; 115=XPAS; 
6=FIX.3.3; 5=1095; 25=8; 24=803; 55=RBCC_MID_PROD; 56=TOP_MID_PROD; 33=N; 52=20210915-12:39:50.139; 115=XNAS; 1133=G; 
24=837; 5=1192; 25=8; 95=stiev; 6=FIX.3.3; 55=RBCC_MID_PROD; 56=TOP_MUD_PROD; 33=N; 52=20210915-12:56:23.691; 115=XNYS; 1133=G; 8101=CARE; 
6=FIX.3.3; 5=1189; 25=8; 95=mwonka; 24=844; 55=RBCC_TOP_PROD; 56=TOP_MUD_PROD; 33=N; 52=20210915-12:59:36.825; 115=XPAS; 1133=G; 8101=CARE; 8115=AOZ; 
;;;;
run;

* Read input file into dataset with record_no and one iten pr. observation;
* Split items in variable number and value;
data csvinput (drop=input_item i compress=char);
	length record_no 8 var 8 value $60 input_item $256;
	infile "c:\temp\file.csv" lrecl=1024 truncover;
	input;
	record_no = _N_;
	i = 0;
	do until (input_item = '');
		i = i + 1;
		input_item = scan(_infile_,i,';');
		var = input(scan(input_item,1,'='),8.);
		value = scan(input_item,2,'=');
		if value ne '' then output;
	end;
run;

* Get distinct list of actual variables in input;
* Data used in step to generate program to read input;
* The longest actual content length of any variable is used to set proper length of output variable;
* Content check on variable is used to set proper type of output variable;
proc sql;
	create table varlist as
		select distinct var, max(length(value)) as varlen, max(notdigit(strip(value))) as type
		from csvinput
		group by var;
quit;

* Build lists of variables;
* Used in keep-, retain- and call missing-statements in step to generate program to read input;
proc sql noprint;
	select distinct 'V' || strip(put(var,8.)) into :allvarlist separated by ' '
	from varlist
	order by var;

	select distinct 'V' || strip(put(var,8.)) into :numvarlist separated by ','
	from varlist
	where type = 0
	order by var;

	select distinct 'V' || strip(put(var,8.)) into :charvarlist separated by ','
	from varlist
	where type > 0
	order by var;

quit;
%put &=allvarlist;
%put &=numvarlist;
%put &=charvarlist;

* Generate data step to read values into proper variables;
data _null_;

	* Start data step;
	if _n_ = 1 then do;
		call execute('data result; set csvinput; by record_no;');
		call execute("keep &allvarlist;");
		call execute("retain &allvarlist;");
	end;

	* Generate attrib statements by looping over varlist;
	do until (eof1);
		set varlist end=eof1;
		if type = 0 then call execute('attrib V' || strip(put(var,8.)) || ' length=8;');
		else call execute('attrib V' || strip(put(var,8.)) || ' length=$' || strip(put(varlen,8.)) ||';');
	end;

	* Set output variables missing before first record_no;
	call execute('if first.record_no then do;');
	call execute("call missing(&numvarlist);");
	call execute("call missing(&charvarlist);");
	call execute('end;');	

	* Generate assign statements by looping over varlist;
	do until (eof2);
		set varlist end=eof2; 
		if type = 0 then call execute('if var = ' || strip(put(var,8.))|| ' then V' || strip(put(var,8.)) || '= input(strip(value),best.);');
		else call execute('if var = ' || strip(put(var,8.)) || ' then V' || strip(put(var,8.)) || '= value;');
	end;

	* Output after last record_no;
	call execute('if last.record_no then output;');

	call execute('run;');
run;