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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 1233 views
  • 0 likes
  • 4 in conversation