topic Reading an input file in SAS Programming

Reading an input file

shubham1 — Thu, 30 Aug 2018 05:09:10 GMT

hello

i have a text file which contain records like this

file have header with table name ,physical name ,descripition ,type all the records are seprtaed by a delimliter #

description is mentioned in multiple lines

tablename,physical_name,descripition,type

T1#customer_data# this is a customer
table with different data
and records which is used
for extraction
#char
T2#accounts# this is account information
which contain accout number and
account details as well
as other
information#char

i want to create a dataset

table, physical name,description and type

How can I read this file

Re: Reading an input file

ChrisNZ — Thu, 30 Aug 2018 05:42:23 GMT

Please show us an example of what the text data actually looks like (alter the values if needed).

Use the {i} icon to paste a couple of example records.

Also show what the expected output looks like for these 2 records.

Re: Reading an input file

shubham1 — Thu, 30 Aug 2018 05:51:45 GMT

this is how dataset look like

Re: Reading an input file

Kurt_Bremser — Thu, 30 Aug 2018 06:03:31 GMT

Please re-read @ChrisNZ's post carefully and thoroughly, and act accordingly. Do NOT post data in pictures. Expecting us to type data off pictures into code is thoughtless at best, but usually considered a sign of laziness, being impolite or plain rude.

If you need help, help us in helping you.

@shubham1 wrote:

this is how dataset look like

Re: Reading an input file

shubham1 — Thu, 30 Aug 2018 06:24:52 GMT

this is how I want output to look like

table_name physical_name descripition type
T1,customer_data,this is a customer table with different dataand records which is used for extraction,char
T2,accounts,this is account information which contain accout number and account details as well as other information,char

Re: Reading an input file

Kurt_Bremser — Thu, 30 Aug 2018 06:34:10 GMT

So your external file has this layout:

T1,customer_data,this is a customer table with different dataand records which is used for extraction,char
T2,accounts,this is account information which contain accout number and account details as well as other information,char

Note how posting text with the {i} icon works. Fixed font, and no possible conversion of special characters.

Try this code for reading it:

data want;
infile cards dlm=',';
input
  tablename :$2.
  physical_name :$32.
  description :$200.
  type :$10.
;
cards;
T1,customer_data,this is a customer table with different dataand records which is used for extraction,char
T2,accounts,this is account information which contain accout number and account details as well as other information,char
;
run;

Note how code posted with the little running man icon looks like. Almost like in the SAS enhanced editor.

For your practical purpose, remove the cards; section and replace the cards keyword with the physical filename of yout infile.

Re: Reading an input file

shubham1 — Thu, 30 Aug 2018 06:39:57 GMT

I am having an txt file which looks like this where records are seprated by # and i want these records to come in four variable in SAS dataset

table_name,physical_name,descripition and type .In the text file records are starting from line 2

tablename,physical_name,descripition,type

i want this txt file to come into a SAS dataset

table_name physical_name descripition type
T1,customer_data,this is a customer table with different data and records which is used for extraction,char
T2,accounts,this is account information which contain account number and account details as well as other information,char

Re: Reading an input file

Kurt_Bremser — Thu, 30 Aug 2018 06:44:06 GMT

Please post the text file AS IS into a window opened with the {i} icon. I won't give any further answers before you do this.

Re: Reading an input file

shubham1 — Thu, 30 Aug 2018 07:00:52 GMT

tablename,physical_name,descripition,type

T1#customer_data# this is a customer
table with different data
and records which is used
for extraction
#char
T2#accounts# this is account information
which contain accout number and
account details as well
as other
information#char

Re: Reading an input file

Kurt_Bremser — Thu, 30 Aug 2018 07:16:39 GMT

If you have line breaks within data that should be a single column, you need to remove these first.

SAS will act on line breaks BEFORE it acts on delimiters, thereby scrambling your file layout.

Searching here on the communities for "line breaks in variables" found me this (currently the 7th hit): https://communities.sas.com/t5/General-SAS-Programming/Carriage-Return-Multiple-Line-Break-Issue-Importing-TXT-File/m-p/420988/highlight/true#M52586

It has a solution for a similar issue, although the deilimiters are pipes instead of hash signs.

Re: Reading an input file

shubham1 — Thu, 30 Aug 2018 08:23:52 GMT

i am not getting how can i use the code that is present in this link to read my txt file

https://communities.sas.com/t5/General-SAS-Programming/Carriage-Return-Multiple-Line-Break-Issue-Importing-TXT-File/m-p/420988/highlight/true#M52586

Re: Reading an input file

Kurt_Bremser — Thu, 30 Aug 2018 09:03:27 GMT

I created a file in my sascommunity test directory with these contents:

tablename,physical_name,descripition,type

T1#customer_data# this is a customer
table with different data
and records which is used
for extraction
#char
T2#accounts# this is account information
which contain accout number and
account details as well
as other
information#char

I then used this code from the link I gave you:

filename sample '$HOME/sascommunity/shubham.txt';

filename fixed '$HOME/sascommunity/shubham_f.txt';

data _null_;
  retain maxp 3 p 0;
  infile sample firstobs=3;
  file fixed ;
  input ;
  if p then put ' ' @;
  put _infile_ @;
  p+countc(_infile_,'#');
  
  if p>= maxp then do ;
    put ;
    p=0;
  end;
run;

Note that there are only four alterations:

the output file is not temporary
the number of expected delimiters is fixed (in the retain statement)
the statement determining the maximum number of delimiters in _n_=1 has been removed
the delimiter has been changed to the hash sign

After that, I ran the following import:

data want;
infile fixed dlm='#' truncover;
input
  tablename :$2.
  physical_name :$32.
  description :$200.
  type :$10.
;
run;

This is how the resulting dataset looks like:

T1	customer_data	this is a customer table with different data and records which is used for extraction	char
T2	accounts	this is account information which contain accout number and account details as well as other information	char

Re: Reading an input file

ChrisNZ — Thu, 30 Aug 2018 10:39:53 GMT

Like this?

data _null_;
file 'c:\temp\t.txt';
put 'tablename,physical_name,descripition,type  ';
put '                                             ';
put 'T1#customer_data# this is a customer         ';
put 'table with different data                    ';
put 'and records which is used                    ';
put 'for extraction                               ';
put '#char                                        ';
put 'T2#accounts# this is account information     ';
put 'which contain accout number and              ';
put 'account details as well                      ';
put 'as other                                     ';
put 'information#char                             ';
run;

data tt;
  infile 'c:\temp\t.txt' firstobs=3;
  input ;                  
  retain DATASET NAME DESC;
  length DATASET NAME DESC $200;
  if countc(_infile_,'#')=2 then do;
    DATASET=scan(_infile_,1,'#');
    NAME   =scan(_infile_,2,'#');
    DESC   =scan(_infile_,3,'#');
  end;
  else if _infile_ ne '#char' then DESC=catx(' ',DESC,scan(_infile_,1,'#'));
  if index(_infile_, '#char') then output;
run;
proc print; run;

Obs	DATASET	NAME	DESC
1	T1	customer_data	this is a customer table with different data and records which is used for extraction
2	T2	accounts	this is account information which contain accout number and account details as well as other information

Please provide complete and clear explanation in your first post next time, and fully answer the questions asked. Don't make it hard to help you.