SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Contributor
Posts: 63
Accepted Solution

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

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 Smiley Happy

 


Accepted Solutions
Solution
‎06-14-2016 03:28 AM
Super User
Posts: 6,936

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Super User
Posts: 7,401

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

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.

Super User
Posts: 5,256

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

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.

Data never sleeps
Solution
‎06-14-2016 03:28 AM
Super User
Posts: 6,936

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 63

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

Thanks @KurtBremser - its working...thanks a lot for your valuable help.
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 405 views
  • 0 likes
  • 4 in conversation