How to read .ok file to reconcile count and variable count

[Riteshdell]

Hello All,

Can any one help me to read .ok file in SAS DI sudio.

I am explaining the problem which I am facing below.

Every month I will get monthly CSV file and .Ok trigger file for same with Date month time stamp.

Take an example-  Csv file name : XYZ_20160430.CSV

.ok File Name - XYZ_20160430.ok

CSV file , simply I am reading as normal DI method.

But in .Ok file sturucture is as like below:

loaddate|30apr2016:10:00:07|

startdate|30apr2016:10:00:07|

count|4500|

pizzz_id|41526|

Note-  this count value is storing number of count of csv file, which will reconcile at next step.

same like pizz_id variable also.

every month , count and pizza_id count can be differnet , which we need to reconcile at next step.

Dynamically, it will change every month.

Please let me know .Ok file reader user written code to read this file.

Thanks in advance 🙂

[RW9]

Sounds like you have created your own delimited file type there, its not a standard one.  Anyways you can read delimeted files in as easily as you can CSV - which is also a delimited file:

data want;
  length param result $200;
  infile "<path>\<file>.ok" dlm="|";
  input param $ result $;
  if param in ("loaddate","startdate") then dte=input(result,datetime.);
  else res_val=input(result,best.);
run;

This will give you a dataset called want, with a column for the first part, a column for the second, and two columns containing either dates, or numbers.

[LinusH]

I can see two possible paths here, which to chose is up to you.

1. This file doen't carry any data warehouse data. So it's not necessary to include that in metadata for that reason. So you could have a validation function on file level implemented as a macro, or similar.

2. You include this file into metadata, And yes, User Written file, or eve a User Written transformation, if you have several files with the same logic/structure. The output should then be transposed (normalized). 

The validation phase should also probably be a User Written transformation, since I don't see that will work seamlessly in a standard transformation.

[Kurt_Bremser · Accepted solution]

You can also collect the data into a single record for output:

data _null_;
file '$HOME/infile';
put 'loaddate|30apr2016:10:00:07|';
put 'startdate|30apr2016:10:00:07|';
put 'count|4500|';
put 'pizza_id|41526|';
run;

data want (keep=loaddate startdate count pizza_id);
infile '$HOME/infile' dlm='|' end=done;
length name $10 value $20;
format
  loaddate
  startdate
    datetime18.
  count
  pizza_id
    5.
;
retain
  loaddate
  startdate
  count
  pizza_id
;
input name value;
select (name);
  when('loaddate') loaddate = input(value,datetime18.);
  when('startdate') startdate = input(value,datetime18.);
  when('count') count = input(value,5.);
  when('pizza_id') pizza_id = input(value,5.);
end;
if done then output;
run;

Depending on what you do with the data, it might also make sense to just create macro variables (with call symput) for use in later code.

[Riteshdell]

Thanks @Kurt_Bremser - its working...thanks a lot for your valuable help.