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?
Good catch, Tom. It is a space.
Which means you have to change the delimiter in the INFILE statement accordingly.
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?
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
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;
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 25. 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.