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;
... View more
