BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Riteshdell
Quartz | Level 8

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 🙂

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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.

View solution in original post

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Tourmaline | Level 20

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
Kurt_Bremser
Super User

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
Quartz | Level 8
Thanks @Kurt_Bremser - its working...thanks a lot for your valuable help.

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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